SET QUOTED_IDENTIFIER ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO

/************** AEUser table ***************/

CREATE TABLE dbo.AEUser
	(
 	UserId 			int 		NOT NULL IDENTITY (1, 1),
	Username 		nvarchar(450) 	NOT NULL,
	UsernameUpper 		nvarchar(450) 	NOT NULL,
	UserSID 		varchar(255) 	NULL 
	) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX AEUser_UserName ON dbo.AEUser
	(
	UsernameUpper
	) ON [PRIMARY]
GO
ALTER TABLE dbo.AEUser ADD CONSTRAINT
	PK_AEUser PRIMARY KEY CLUSTERED 
	(
	UserId
	) ON [PRIMARY]
GO


/************** BlobTable ***************/
CREATE TABLE dbo.BlobTable
	(
 	BlobId 			int 		NOT NULL IDENTITY (1, 1),
	BlobSecret 		varchar(32) 	NOT NULL,
	BlobFileExt 		nvarchar(8) 	NOT NULL,
	BlobGUID 		char(38) 	NOT NULL,
	BlobExecFlag 		tinyint 	NOT NULL,
	BlobMetaFlag 		tinyint 	NOT NULL,
	BlobTimeCreated 	datetime 	NOT NULL,
	BlobSize 		int 		NOT NULL,
	BlobData 		image 		NULL
	) ON [PRIMARY]
	 TEXTIMAGE_ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX BlobTable_BlobGUID ON dbo.BlobTable
	(
	BlobGUID
	) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX BlobTable_BlobTimeCreated ON dbo.BlobTable
	(
	BlobTimeCreated
	) ON [PRIMARY]
GO
ALTER TABLE dbo.BlobTable ADD CONSTRAINT
	PK_BlobTable PRIMARY KEY CLUSTERED 
	(
	BlobId
	) ON [PRIMARY]
GO

/************** Node table ***************/
CREATE TABLE dbo.Node
	(
 	Id 			int 		NOT NULL IDENTITY (1, 1),
 	
	IsLocked 		tinyint 	NOT NULL,
	IsCheckedOut 		tinyint 	NOT NULL,
	IsCheckedOutVersion 	tinyint 	NOT NULL,
	HasCheckedInVersion 	tinyint 	NOT NULL,
	CheckedInVersionHasExisted tinyint 	NOT NULL,
	IsShortcut 		tinyint 	NOT NULL,
	IsBeingInserted 	tinyint 	NOT NULL,
	
	IsHighPriority 		tinyint 	NOT NULL,
	SameRightsAsParent 	tinyint 	NOT NULL,
	ReadyForApproval 	tinyint 	NOT NULL,
	
	ArchiveState 		int 		NOT NULL,
	ApprovalStatus 		int 		NOT NULL,
	Type 			int 		NOT NULL,
	SortOrdinal 		int 		NOT NULL,
	
	NodeGUID 		char(38) 	NOT NULL,
	ParentGUID 		char(38) 	NULL,
	FollowGUID 		char(38) 	NULL,
	DeletedWhen		datetime	NULL,
	DeletedByUserId		int		NULL,
	CreatedByUserId 	int 		NOT NULL,
	CreatedWhen 		datetime 	NOT NULL,
	ModifiedByUserId 	int 		NOT NULL,
	ModifiedWhen 		datetime 	NOT NULL,
	OwnerId	 		int 		NOT NULL,
	ApprovedByUserId 	int 		NULL,
	EffectiveDate 		datetime 	NULL,
	ExpiryDate 		datetime 	NULL,
	TemplateGUID 		varchar(38) 	NULL,
	DerivedFromGUID		varchar(38)	NULL,
	ControlType 		int 		NULL,
	EmitterGUID 		varchar(38) 	NULL,
	RuntimeControlGUID 	varchar(38) 	NULL,
	DesigntimeControlGUID 	varchar(38) 	NULL,
	ParserGUID 		varchar(38) 	NULL,
	IsBaseTemplate		tinyint		NULL,

	ArchiveSourceGUID 	char(38) 	NULL,
	ArchivedParentGUID 	char(38) 	NULL,
	
	ApprovedWhen 		datetime 	NULL,
	ArchivedWhen 		datetime 	NULL,

	LockUserId 		int 		NULL,
	LockMachinename 	nvarchar(50) 	NULL,
	
	Name 			nvarchar(250) 	NULL,
	TemplateType 		nvarchar(150) 	NULL,
	Mimetype 		nvarchar(50) 	NULL,
	DirectoryService 	nvarchar(500)	NULL,
	DirectoryGUID 		char(38) 		NULL,	
	
	IsHiddenModePublished	tinyint 	NULL,
	IsRobotFollowable	tinyint 	NULL,
	IsRobotIndexable	tinyint 	NULL,
	DisplayName		nvarchar(250) 	NULL,

	LastChangeTime 		datetime 	NOT NULL,
	) ON [PRIMARY]
GO

ALTER TABLE dbo.Node ADD CONSTRAINT
	PK_Node PRIMARY KEY NONCLUSTERED 
	(
	Id
	) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX Node_LastChangeTime ON dbo.Node
	(
	LastChangeTime
	) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX Node_NodeGUID ON dbo.Node
	(
	NodeGUID,
	IsCheckedOutVersion
	) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX Node_ArchiveSourceGUID ON dbo.Node
	(
	ArchiveSourceGUID
	) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX Node_CreatedByUserId ON dbo.Node
	(
	CreatedByUserId,
	Id
	) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX Node_FollowGUID ON dbo.Node
	(
	FollowGUID
	) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX Node_ModifiedWhen ON dbo.Node
	(
	ModifiedWhen
	) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX Node_Name ON dbo.Node
	(
	Name
	) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX Node_OwnerId ON dbo.Node
	(
	OwnerId
	) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX Node_ParentGUID ON dbo.Node
	(
	ParentGUID
	) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX Node_Type ON dbo.Node
	(
	Type
	) ON [PRIMARY]
GO

ALTER TABLE dbo.Node ADD CONSTRAINT
	FK_Node_CreatedByUserID FOREIGN KEY
	(
	CreatedByUserID
	) REFERENCES dbo.AEUser
	(
	UserId
	)
go


ALTER TABLE dbo.Node ADD CONSTRAINT
	FK_Node_ModifiedByUserID FOREIGN KEY
	(
	ModifiedByUserID
	) REFERENCES dbo.AEUser
	(
	UserId
	)
GO

ALTER TABLE dbo.Node ADD CONSTRAINT
	FK_Node_ApprovedByUserID FOREIGN KEY
	(
	ApprovedByUserID
	) REFERENCES dbo.AEUser
	(
	UserId
	)
GO

ALTER TABLE dbo.Node ADD CONSTRAINT
	FK_Node_DeletedByUserID FOREIGN KEY
	(
	DeletedByUserID
	) REFERENCES dbo.AEUser
	(
	UserId
	)
GO

ALTER TABLE dbo.Node ADD CONSTRAINT
	FK_Node_OwnerID FOREIGN KEY
	(
	OwnerID
	) REFERENCES dbo.AEUser
	(
	UserId
	)
go

/************** NodeLock table ***************/
CREATE TABLE dbo.NodeLock
	(
 	NodeId 				int 		NOT NULL,
 	
	NewIsHighPriority 		tinyint 	NOT NULL,
	NewSameRightsAsParent 		tinyint 	NOT NULL,
	NewReadyForApproval 		tinyint 	NOT NULL,
	NewSortOrdinal 			int 		NOT NULL,
	
	NewApprovalStatus		int		NOT NULL,
	NewApprovedByUserId	 	int 		NULL,
	NewOwnerId	 		int 		NOT NULL,
	NewEffectiveDate 		datetime 	NULL,
	NewExpiryDate 			datetime 	NULL,
	NewTemplateGUID 		varchar(38) 	NULL,
	NewControlType 			int 		NULL,
	NewEmitterGUID 			varchar(38) 	NULL,
	NewRuntimeControlGUID 		varchar(38) 	NULL,
	NewDesigntimeControlGUID 	varchar(38) 	NULL,

	NewName 			nvarchar(250) 	NULL,
	NewTemplateType 		nvarchar(150) 	NULL,
	NewDirectoryService 		nvarchar(500)	NULL,
	NewDirectoryGUID	 		char(38) 	NULL,

	NewIsHiddenModePublished	bit NULL,
	NewIsRobotFollowable		bit NULL,
	NewIsRobotIndexable			bit NULL,
	NewDisplayName				nvarchar(250) NULL

	) ON [PRIMARY]
	
GO

ALTER TABLE dbo.NodeLock ADD CONSTRAINT
	PK_NodeLock PRIMARY KEY CLUSTERED 
	(
	NodeId
	) ON [PRIMARY]
GO

ALTER TABLE dbo.NodeLock ADD CONSTRAINT
	FK_NodeLock_Node FOREIGN KEY
	(
	NodeId
	) REFERENCES dbo.Node
	(
	Id
	)
GO

ALTER TABLE dbo.NodeLock ADD CONSTRAINT
	FK_NodeLock_NewApprovedByUserID FOREIGN KEY
	(
	NewApprovedByUserID
	) REFERENCES dbo.AEUser
	(
	UserId
	)
GO

ALTER TABLE dbo.NodeLock ADD CONSTRAINT
	FK_NodeLock_NewOwnerID FOREIGN KEY
	(
	NewOwnerID
	) REFERENCES dbo.AEUser
	(
	UserId
	)
GO

/************** UserRoleMember table ***************/
CREATE TABLE dbo.UserRoleMember
	(
 	NodeId int NOT NULL,
	UserId int NOT NULL,
	IsBeingInserted tinyint NOT NULL,
	IsBeingDeleted tinyint NOT NULL,
	IsBeingUpdated tinyint NOT NULL
	) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX URM_UserId ON dbo.UserRoleMember
	(
	UserId
	) ON [PRIMARY]
GO
ALTER TABLE dbo.UserRoleMember ADD CONSTRAINT
	PK_UserRoleMember PRIMARY KEY CLUSTERED 
	(
	NodeId,
	UserId
	) ON [PRIMARY]
GO

ALTER TABLE dbo.UserRoleMember ADD CONSTRAINT
	FK_UserRoleMember_Node FOREIGN KEY
	(
	NodeId
	) REFERENCES dbo.Node
	(
	Id
	)
GO
ALTER TABLE dbo.UserRoleMember ADD CONSTRAINT
	FK_UserRoleMember_AEUser FOREIGN KEY
	(
	UserId
	) REFERENCES dbo.AEUser
	(
	UserId
	)
GO


/************** NodeResource table ***************/
CREATE TABLE dbo.NodeResource
	(
 	NodeId 				int		NOT NULL,
	Id 				int 		NOT NULL,
	Name				nvarchar(100) 	NOT NULL,
	IsLink 				tinyint 	NOT NULL,
	ResourceBlobId 			int 		NULL,
	NodeGUID			char(38) 	NULL,
	LinkAnchor 			nvarchar(255) 	NULL,
	IsBeingInserted 		tinyint 	NOT NULL,
	IsBeingDeleted 			tinyint 	NOT NULL,
	IsBeingUpdated			tinyint		NOT NULL,

	NewName 			nvarchar(100) 	NULL,
	NewIsLink 			tinyint 	NULL,
	NewResourceBlobId 		int 		NULL,
	NewNodeGUID 			char(38) 	NULL,
	NewLinkAnchor 			nvarchar(255) 	NULL,
	URL 				ntext 		NULL,
	NewURL 				ntext 		NULL
	) ON [PRIMARY]
	 TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE dbo.NodeResource ADD CONSTRAINT
	PK_NodeResource PRIMARY KEY CLUSTERED 
	(
	NodeId,
	Id
	) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX NodeResource_ResourceBlobId ON dbo.NodeResource
	(
	ResourceBlobId
	) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX NodeResource_NodeGUID ON dbo.NodeResource
	(
	NodeGUID
	) ON [PRIMARY]
GO

ALTER TABLE dbo.NodeResource ADD CONSTRAINT
	FK_NodeResource_Node FOREIGN KEY
	(
	NodeId
	) REFERENCES dbo.Node
	(
	Id
	)
GO

ALTER TABLE dbo.NodeResource ADD CONSTRAINT
	FK_NodeResource_BlobTable FOREIGN KEY
	(
	ResourceBlobId
	) REFERENCES dbo.BlobTable
	(
	BlobId
	)
GO

ALTER TABLE dbo.NodeResource ADD CONSTRAINT
	FK_NewNodeResource_BlobTable FOREIGN KEY
	(
	NewResourceBlobId
	) REFERENCES dbo.BlobTable
	(
	BlobId
	)
GO

/************** NodeProperty table ***************/
CREATE TABLE dbo.NodeProperty
	(
 	NodeId 				int 		NOT NULL,
	Id 				int 		NOT NULL,
	PropName 			nvarchar(50) 	NOT NULL,
	PropValue 			nvarchar(300)	NULL,
	PropType			int	NOT NULL,
	IsBeingInserted 		tinyint 	NOT NULL,
	IsBeingDeleted 			tinyint 	NOT NULL,
	IsBeingUpdated			tinyint		NOT NULL,
	
	NewPropValue 			nvarchar(300)	NULL,
	
	PropValueLong			ntext			NULL,
	NewPropValueLong		ntext			NULL
	) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX NodeProperty_NameValue ON dbo.NodeProperty
	(
	PropName,
	PropValue
	) ON [PRIMARY]

CREATE NONCLUSTERED INDEX NodeProperty_Value ON dbo.NodeProperty
	(
	PropValue
	) ON [PRIMARY]
GO

ALTER TABLE dbo.NodeProperty ADD CONSTRAINT
	PK_NodeProperty PRIMARY KEY CLUSTERED 
	(
	NodeId,
	Id
	) ON [PRIMARY]
GO


ALTER TABLE dbo.NodeProperty ADD CONSTRAINT
	FK_NodeProperty_Node FOREIGN KEY
	(
	NodeId
	) REFERENCES dbo.Node
	(
	Id
	)
GO

/************** LayoutTemplateProperty table ***************/
CREATE TABLE dbo.LayoutTemplateProperty
	(
 	NodeId 				int 		NOT NULL,
	Id 				int 		NOT NULL,
	PropName 			nvarchar(100) 	NOT NULL,
	IsControl 			tinyint 	NOT NULL,
	ControlId 			int 		NULL,
	IsBeingInserted 		tinyint 	NOT NULL,
	IsBeingDeleted 			tinyint 	NOT NULL,
	IsBeingUpdated			tinyint		NOT NULL,
	
	NewPropName 			nvarchar(50) 	NULL,
	NewIsControl 			tinyint 	NULL,
	NewControlId 			int 		NULL,
	
	PropValue 			ntext 		NULL,
	NewPropValue 			ntext 		NULL
	) ON [PRIMARY]
	 TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE dbo.LayoutTemplateProperty ADD CONSTRAINT
	PK_LayoutTemplateProperty PRIMARY KEY CLUSTERED 
	(
	NodeId,
	Id
	) ON [PRIMARY]
GO

ALTER TABLE dbo.LayoutTemplateProperty ADD CONSTRAINT
	FK_LayoutTemplateProperty_Node FOREIGN KEY
	(
	NodeId
	) REFERENCES dbo.Node
	(
	Id
	)

GO

/************** GalleryControl table ***************/
CREATE TABLE dbo.GalleryControl
	(
 	NodeId 				int 		NOT NULL,
	Id 				int 		NOT NULL,
	ActiveXGUID 			char(38) 	NULL,
	Codebase 			nvarchar(255) 	NULL,
	Name 				nvarchar(50) 	NULL,
	Platform 			nvarchar(255) 	NULL,
	Version 			varchar(50) 	NULL,
	IsBeingInserted 		tinyint 	NOT NULL,
	IsBeingDeleted 			tinyint 	NOT NULL,
	IsBeingUpdated			tinyint		NOT NULL,

	NewActiveXGUID 			char(38) 	NULL,
	NewCodebase 			nvarchar(255)	NULL,
	NewName 			nvarchar(50)	NULL,
	NewPlatform 			nvarchar(255)	NULL,
	NewVersion 			varchar(50)	NULL
	) ON [PRIMARY]
GO

ALTER TABLE dbo.GalleryControl ADD CONSTRAINT
	PK_GalleryControl PRIMARY KEY CLUSTERED 
	(
	NodeId,
	Id
	) ON [PRIMARY]
GO

ALTER TABLE dbo.GalleryControl ADD CONSTRAINT
	FK_GalleryControl_Node FOREIGN KEY
	(
	NodeId
	) REFERENCES dbo.Node
	(
	Id
	)
GO

/************** NodeControl table ***************/
CREATE TABLE dbo.NodeControl
	(
 	NodeId 			int 		NOT NULL,
	Id 			int 		NOT NULL,
	Name 			nvarchar(100) 	NOT NULL,
	ControlGUID 		char(38) 	NOT NULL,
	IsBeingInserted 	tinyint 	NOT NULL,
	IsBeingDeleted 		tinyint 	NOT NULL,
	IsBeingUpdated		tinyint		NOT NULL,
	
	NewName 		nvarchar(100) 	NULL,
	) ON [PRIMARY]
GO

ALTER TABLE dbo.NodeControl ADD CONSTRAINT
	PK_NodeControl PRIMARY KEY CLUSTERED 
	(
	NodeId,
	Id
	) ON [PRIMARY]
GO

ALTER TABLE dbo.NodeControl ADD CONSTRAINT
	UQ_NodeControl UNIQUE 
	(
	NodeId,
	Name,
	IsBeingDeleted
	) ON [PRIMARY]
GO

ALTER TABLE dbo.NodeControl ADD CONSTRAINT
	FK_NodeControl_Node FOREIGN KEY
	(
	NodeId
	) REFERENCES dbo.Node
	(
	Id
	)
GO

/************** ControlProperty table ***************/
CREATE TABLE dbo.ControlProperty
	(
 	NodeId 			int 		NOT NULL,
	ControlId 		int 		NOT NULL,
	Id 			int 		NOT NULL,
	PropName 		nvarchar(100) 	NOT NULL,
	IsResource 		tinyint 	NOT NULL,
	ResourceId 		int 		NULL,
	IsBeingInserted 	tinyint 	NOT NULL,
	IsBeingDeleted 		tinyint 	NOT NULL,
	IsBeingUpdated		tinyint		NOT NULL,
	
	NewPropName 		nvarchar(100) 	NULL,
	NewIsResource		tinyint 	NULL,
	NewResourceId 		int 		NULL,
	
	PropValue 		ntext 		NULL,
	NewPropValue 		ntext 		NULL
	) ON [PRIMARY]
	 TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE dbo.ControlProperty ADD CONSTRAINT
	PK_ControlProperty PRIMARY KEY CLUSTERED 
	(
	NodeId,
	ControlId,
	Id
	) ON [PRIMARY]
GO

ALTER TABLE dbo.ControlProperty ADD CONSTRAINT
	FK_ControlProperty_NodeControl FOREIGN KEY
	(
	NodeId,
	ControlId
	) REFERENCES dbo.NodeControl
	(
	NodeId,
	Id
	)
GO

/************** NodeLayout table ***************/
CREATE TABLE dbo.NodeLayout
	(
 	NodeId 				int 		NOT NULL,
	Id 				int 		NOT NULL,
	Name 				nvarchar(255) 	NOT NULL,
	LayoutTemplateGUID 		char(38) 	NULL,
	IsBeingInserted 		tinyint 	NOT NULL,
	IsBeingDeleted 			tinyint 	NOT NULL,
	IsBeingUpdated			tinyint		NOT NULL,
	
	NewName 			nvarchar(255) 	NULL,
	NewLayoutTemplateGUID 		char(38) 	NULL
	) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX NodeLayout_LayoutTemplateGUID ON dbo.NodeLayout
	(
	LayoutTemplateGUID
	) ON [PRIMARY]
GO
ALTER TABLE dbo.NodeLayout ADD CONSTRAINT
	PK_NodeLayout PRIMARY KEY CLUSTERED 
	(
	NodeId,
	Id
	) ON [PRIMARY]
GO

ALTER TABLE dbo.NodeLayout ADD CONSTRAINT
	FK_NodeLayout_Node FOREIGN KEY
	(
	NodeId
	) REFERENCES dbo.Node
	(
	Id
	)
GO

/************** LayoutProperty table ***************/
CREATE TABLE dbo.LayoutProperty
	(
 	NodeId 			int 		NOT NULL,
	LayoutId 		int 		NOT NULL,
	Id 			int 		NOT NULL,
	PropName 		nvarchar(100) 	NOT NULL,
	IsControl 		tinyint 	NOT NULL,
	ControlId 		int 		NULL,
	
	IsBeingInserted 	tinyint 	NOT NULL,
	IsBeingDeleted 		tinyint 	NOT NULL,
	IsBeingUpdated		tinyint		NOT NULL,
	
	NewPropName 		nvarchar(100) 	NULL,
	NewIsControl 		tinyint 	NULL,
	NewControlId 		int 		NULL,
	
	PropValue 		ntext 		NULL,
	NewPropValue 		ntext 		NULL
	) ON [PRIMARY]
	 TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE dbo.LayoutProperty ADD CONSTRAINT
	PK_LayoutProperty PRIMARY KEY CLUSTERED 
	(
	NodeId,
	LayoutId,
	Id
	) ON [PRIMARY]
GO

ALTER TABLE dbo.LayoutProperty ADD CONSTRAINT
	FK_LayoutProperty_NodeLayout FOREIGN KEY
	(
	NodeId,
	LayoutId
	) REFERENCES dbo.NodeLayout
	(
	NodeId,
	Id
	)
GO


/************** NodeRole table ***************/
CREATE TABLE dbo.NodeRole
	(
 	NodeId 			int 		NOT NULL,
	RoleGUID 		char(38) 	NOT NULL,
	
	IsBeingInserted 	tinyint 	NOT NULL,
	IsBeingDeleted 		tinyint 	NOT NULL,
	IsBeingUpdated		tinyint		NOT NULL
	) ON [PRIMARY]
GO

ALTER TABLE dbo.NodeRole ADD CONSTRAINT
	PK_NodeRole PRIMARY KEY CLUSTERED 
	(
	NodeId,
	RoleGUID
	) ON [PRIMARY]
GO

ALTER TABLE dbo.NodeRole ADD CONSTRAINT
	FK_NodeRole_Node FOREIGN KEY
	(
	NodeId
	) REFERENCES dbo.Node
	(
	Id
	)
GO

/************** UserRoleNode table ***************/
CREATE TABLE dbo.UserRoleNode
	(
 	NodeId 		int 	NOT NULL,
	NodeType 	int 	NOT NULL,
	Rights 		int 	NOT NULL,
	NewRights 	int 	NULL,
	
	IsBeingInserted 	tinyint 	NOT NULL,
	IsBeingDeleted 		tinyint 	NOT NULL,
	IsBeingUpdated		tinyint		NOT NULL
	) ON [PRIMARY]
GO

ALTER TABLE dbo.UserRoleNode ADD CONSTRAINT
	PK_UserRoleNode PRIMARY KEY CLUSTERED 
	(
	NodeId,
	NodeType
	) ON [PRIMARY]
GO

ALTER TABLE dbo.UserRoleNode ADD CONSTRAINT
	FK_UserRoleNode_Node FOREIGN KEY
	(
	NodeId
	) REFERENCES dbo.Node
	(
	Id
	)
GO

/************** ChangeTable table ***************/
CREATE TABLE dbo.ChangeTable
	(
	NodeGUID	char(38) NOT NULL,
	ParentGUID	char(38) null,
	Reason 		int	NOT NULL,
	LastChangeTime datetime NOT NULL,
	CommitTime datetime NOT NULL
	)
Go

ALTER TABLE dbo.ChangeTable ADD CONSTRAINT
	PK_ChangeTable Primary Key Nonclustered
	(
	NodeGUID
	)
Go
CREATE clustered Index ChangeTable_CommitTime On dbo.ChangeTable
	(
	CommitTime
	)
Go

create table Utility
	(
	DeltaGMT int,
	LastRevisionPurge datetime null
	)
go

insert into utility (LastRevisionPurge) values('1900-01-01 00:00:00')
go

create table ServerLock
	(
	IsLocked tinyint not null PRIMARY KEY CLUSTERED
	)
go

insert into ServerLock values(0)
go


CREATE TABLE dbo.ExcludeList
	 (
	 NodeGuid char(38) NOT NULL PRIMARY KEY CLUSTERED,
	 Depth int NULL 
) ON [PRIMARY]
GO

CREATE  INDEX ExcludeList_Depth ON dbo.ExcludeList(Depth) ON [PRIMARY]
GO

CREATE TABLE dbo.IncludeList 
	(
	NodeGuid char(38) NOT NULL PRIMARY KEY CLUSTERED,
	ParentGUID char(38) NULL,
	Type int NULL,
	Depth int NULL,
	IsShortcut int NULL 
) ON [PRIMARY]
GO

CREATE  INDEX IncludeList_Depth ON dbo.IncludeList(Depth) ON [PRIMARY]
GO


CREATE TABLE dbo.Patch (
	PatchId int NOT NULL ,
	PatchDescript nvarchar(255) NOT NULL ,
	UpdateTime datetime NOT NULL 
) ON [PRIMARY]
GO

 CREATE  UNIQUE  CLUSTERED  INDEX [Patch_PK] ON [dbo].[Patch]([PatchId]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO


/********** Defaults ***************/
alter table AEUser Add constraint UserNameUpper_Default 
DEFAULT '' for UserNameUpper
go

alter table Node add constraint Node_CreatedWhen_Default
DEFAULT '1900-01-01 00:00:00' for CreatedWhen
go

alter table Node add constraint Node_ModifiedWhen_Default
DEFAULT '1900-01-01 00:00:00' for ModifiedWhen
go

alter table Node add constraint Node_LastChangeTime_Default
DEFAULT '1900-01-01 00:00:00' for LastChangeTime
go

alter table NodeProperty add constraint NodePropery_PropType_Default
DEFAULT 0 for PropType
go

alter table ChangeTable add constraint ChangeTable_Reason_Default
DEFAULT 0 for Reason
go


/********************** Stored Procedures ********************/
declare @SQL varchar(500)
if substring(@@VERSION, PATINDEX('%_.__.___%', @@VERSION), 1) = '8'
	select @SQL = 'create procedure GetGMTTime ' + 
		'@dateCurrent datetime, @dateGMT datetime output with encryption ' +
	'as	set nocount on select @dateGMT = getutcdate()'

else
	select @SQL = 'create procedure GetGMTTime ' + 
		'@dateCurrent datetime, @dateGMT datetime output  with encryption ' + 
		'as set nocount on DECLARE @deltaGMT int ' + 
		'select @deltaGMT = DeltaGMT from Utility ' + 
		'SELECT @dateGMT = DATEADD(minute, @deltaGMT, @dateCurrent )'

exec (@SQL)
go


Create Procedure DeleteNodeByGUID
	(
		@NodeGUID varchar(38)
	) 
	with encryption
As
 SET NOCOUNT ON 
 
 DECLARE @CurrentId as int

 DECLARE DeletedItems CURSOR LOCAL READ_ONLY 
    FOR SELECT Id FROM Node WHERE NodeGUID = @NodeGUID 

  OPEN DeletedItems
  FETCH NEXT FROM DeletedItems INTO @CurrentId
  WHILE @@FETCH_STATUS = 0
  BEGIN
		DELETE FROM NodeProperty WHERE NodeId = @CurrentId
		DELETE FROM NodeRole WHERE NodeId = @CurrentId
		DELETE FROM NodeResource WHERE NodeId = @CurrentId
		DELETE FROM ControlProperty WHERE NodeId = @CurrentId
		DELETE FROM NodeControl WHERE NodeId = @CurrentId
		DELETE FROM LayoutProperty WHERE NodeId = @CurrentId
		DELETE FROM NodeLayout WHERE NodeId = @CurrentId
		DELETE FROM GalleryControl WHERE NodeId = @CurrentId
		DELETE FROM LayoutTemplateProperty WHERE NodeId = @CurrentId
		DELETE FROM NodeRole WHERE NodeId = @CurrentId
		DELETE FROM UserRoleMember WHERE NodeId = @CurrentId
		DELETE FROM UserRoleNode WHERE NodeId = @CurrentId
		DELETE FROM NodeLock WHERE NodeId = @CurrentId
		DELETE FROM Node WHERE Id = @CurrentId
		
		FETCH NEXT FROM DeletedItems INTO @CurrentId
  END  

  CLOSE DeletedItems
  DEALLOCATE DeletedItems

return
GO

create Procedure DeleteNodeById
	(
		@CurrentId int,
		@EmptyTables int
	)
	with encryption
As
 SET NOCOUNT ON
  
	if ( @EmptyTables & 2 ) = 0 DELETE FROM NodeProperty WHERE NodeId = @CurrentId
	if ( @EmptyTables & 1 ) = 0 DELETE FROM NodeRole WHERE NodeId = @CurrentId
	if ( @EmptyTables & 16 ) = 0 DELETE FROM NodeResource WHERE NodeId = @CurrentId
	if ( @EmptyTables & 4 ) = 0 DELETE FROM ControlProperty WHERE NodeId = @CurrentId
	if ( @EmptyTables & 32 ) = 0 DELETE FROM NodeControl WHERE NodeId = @CurrentId
	DELETE FROM LayoutTemplateProperty WHERE NodeId = @CurrentId
	if ( @EmptyTables & 64 ) = 0 DELETE FROM LayoutProperty WHERE NodeId = @CurrentId
	if ( @EmptyTables & 8 ) = 0 DELETE FROM NodeLayout WHERE NodeId = @CurrentId
	/*DELETE FROM GalleryControl WHERE NodeId = @CurrentId*/
	DELETE FROM UserRoleMember WHERE NodeId = @CurrentId
	if ( @EmptyTables & 128 ) = 0 DELETE FROM UserRoleNode WHERE NodeId = @CurrentId
	DELETE FROM NodeLock WHERE NodeId = @CurrentId
	DELETE FROM Node WHERE Id = @CurrentId
return
GO

create  procedure GetTimeZoneSetting
with encryption
as
if substring(@@VERSION, PATINDEX('%_.__.___%', @@VERSION), 1) <> '8'
begin
	DECLARE @deltaGMT int
	
	EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
	'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
	'ActiveTimeBias', @deltaGMT OUT
	
	if exists (select * from utility )
		update utility set deltaGMT = @deltaGMT
	else
		insert into Utility (DeltaGMT) values (@deltaGMT)
end

GO

create  procedure UpdateChangeTable
with encryption
as
exec sp_executesql N'DECLARE @dateGMT datetime, @dateCurrent datetime, @MaxCommit datetime, @CommitTime datetime
select @dateCurrent = GETDATE()

exec GetGMTTime @dateCurrent, @dateGMT out

if exists(select nodeguid from #Sandbox)
begin
	select  @MaxCommit = MAX( CommitTime ) from ChangeTable WITH(UPDLOCK) 
	select  @commitTime = case when @dateGMT <= @MaxCommit then DateAdd( ms, 3, @MaxCommit ) else @dateGMT END

	UPDATE ChangeTable 
			SET Reason = nodes.Reason, 
			LastChangeTime = nodes.LastChangeTime,
			CommitTime = @CommitTime,
			ParentGUID = nodes.ParentGUID
		 from #Sandbox nodes
		 where ChangeTable.NodeGUID = nodes.NodeGUID

	 INSERT INTO ChangeTable (NodeGuid, ParentGuid, Reason, LastChangeTime, CommitTime) 
		select NodeGUID, ParentGUID, Reason, LastChangeTime,@CommitTime from #Sandbox  
			where nodeguid not in (select nodeguid from ChangeTable)
end'
GO


create  PROCEDURE CopyAssociatedData( @SourceID int, 
				      @DestID int, 
			          @CopyFromLock int, 
				      @NodeType int,
				      @EmptyTables int )
with encryption
AS

set nocount on

/* These are the values for empty table bitfields:
NO_ROLE_ACL = 1,	NodeRole
NO_GENERIC_PROPS = 2,	NodeProperty
NO_CONTROL_ITEMS = 4,	ControlProp
NO_LAYOUTS = 8,		NodeLayout
NO_RESOURCES = 16,	NodeResource
NO_CONTROLS = 32,	NodeControl
NO_LAYOUT_PROPS = 64,	LayoutProp
NO_ROLE_RIGHTS = 128, 	UserRoleNode
*/
IF @CopyFromLock = 1
BEGIN

	/* Layout Templates */
	if @nodeType = 0x10000
	begin
		INSERT INTO LayoutTemplateProperty (IsBeingInserted, IsBeingUpdated, IsBeingDeleted, NodeId, Id, PropName, PropValue, IsControl, ControlId) 
			SELECT 0, 0, 0, @DestID, Id, NewPropName, NewPropValue, NewIsControl, NewControlId FROM LayoutTemplateProperty WHERE NodeId = @SourceID AND IsBeingDeleted = 0 and ( isBeingInserted = 1 or isbeingupdated = 1 )
		INSERT INTO LayoutTemplateProperty (IsBeingInserted, IsBeingUpdated, IsBeingDeleted, NodeId, Id, PropName, PropValue, IsControl, ControlId)
			SELECT 0, 0, 0, @DestID, Id, PropName, PropValue, IsControl, ControlId FROM LayoutTemplateProperty WHERE NodeId = @SourceID AND ( IsBeingInserted = 0 AND IsBeingUpdated = 0 )
	end

	/* User role */
	if @nodeType = 0x100000
	begin
		INSERT INTO UserRoleMember (IsBeingInserted, IsBeingUpdated, IsBeingDeleted, NodeId, UserId)
			SELECT 0, 0, 0, @DestID, UserId FROM UserRoleMember WHERE NodeId = @SourceID AND IsBeingDeleted = 0

		if ( @EmptyTables & 128 ) = 0
		begin
			INSERT INTO UserRoleNode (IsBeingInserted, IsBeingUpdated, IsBeingDeleted, NodeId, NodeType, Rights)
				SELECT 0, 0, 0,@DestID, NodeType, NewRights FROM UserRoleNode WHERE NodeId = @SourceID AND IsBeingDeleted = 0 and ( isBeingInserted = 1 or isbeingupdated = 1 )  
			INSERT INTO UserRoleNode (IsBeingInserted, IsBeingUpdated, IsBeingDeleted, NodeId, NodeType, Rights)
				SELECT 0, 0, 0, @DestID, NodeType, Rights FROM UserRoleNode WHERE NodeId = @SourceID  AND ( IsBeingInserted = 0 AND IsBeingUpdated = 0 )
		end
	end
	
/*	if @nodeType = 0x00000400 or @nodeType = 0x80000800 or @nodeType = 0x00001000
		INSERT INTO GalleryControl (IsBeingInserted, IsBeingUpdated, IsBeingDeleted, NodeId, Id, ActiveXGUID, Codebase, Name, Platform, Version)
			SELECT 0, 0, 0, @DestID, Id, NewActiveXGUID, NewCodeBase, NewName, NewPlatform, NewVersion FROM GalleryControl WHERE NodeId = @SourceID AND IsBeingDeleted = 0
*/
	/* If we are not a shortcut... */
	if @nodetype <> 0x80000020
	begin
		if ( @EmptyTables & 16 ) = 0
		begin
			INSERT INTO NodeResource (IsBeingInserted, IsBeingUpdated, IsBeingDeleted, NodeId, Id, Name, IsLink, URL, ResourceBlobId, NodeGUID, LinkAnchor) 
				SELECT 0, 0, 0, @DestID, Id, NewName, NewIsLink, NewURL, NewResourceBlobId, NewNodeGUID, NewLinkAnchor FROM NodeResource WHERE NodeId = @SourceID AND IsBeingDeleted = 0 and ( isBeingInserted = 1 or isbeingupdated = 1 )
			INSERT INTO NodeResource (IsBeingInserted, IsBeingUpdated, IsBeingDeleted, NodeId, Id, Name, IsLink, URL, ResourceBlobId, NodeGUID, LinkAnchor) 
				SELECT 0, 0, 0, @DestID, Id, Name, IsLink, URL, ResourceBlobId, NodeGUID, LinkAnchor FROM NodeResource WHERE NodeId = @SourceID AND ( IsBeingInserted = 0 AND IsBeingUpdated = 0 )
		end
		if ( @EmptyTables & 32 ) = 0
		begin
			INSERT INTO NodeControl (IsBeingInserted, IsBeingUpdated, IsBeingDeleted, NodeId, Id, NewName, ControlGUID) 
				SELECT 0, 0, 0, @DestID, Id, NewName, ControlGUID FROM NodeControl WHERE NodeId = @SourceID AND IsBeingDeleted = 0 and ( isBeingInserted = 1 or isbeingupdated = 1 )
			INSERT INTO NodeControl (IsBeingInserted, IsBeingUpdated, IsBeingDeleted, NodeId, Id, Name, ControlGUID) 
				SELECT 0, 0, 0, @DestID, Id, Name, ControlGUID FROM NodeControl WHERE NodeId = @SourceID  AND ( IsBeingInserted = 0 AND IsBeingUpdated = 0 )
		end
		if ( @EmptyTables & 4 ) = 0
		begin
			INSERT INTO ControlProperty (IsBeingInserted, IsBeingUpdated, IsBeingDeleted, NodeId, ControlId, Id, PropName, IsResource, PropValue, ResourceId) 
				SELECT 0, 0, 0, @DestID, ControlId, Id, NewPropName, NewIsResource, NewPropValue, NewResourceId FROM ControlProperty WHERE NodeId = @SourceID AND IsBeingDeleted = 0 and ( isBeingInserted = 1 or isbeingupdated = 1 )
			INSERT INTO ControlProperty (IsBeingInserted, IsBeingUpdated, IsBeingDeleted, NodeId, ControlId, Id, PropName, IsResource, PropValue, ResourceId) 
				SELECT 0, 0, 0, @DestID, ControlId, Id, PropName, IsResource, PropValue, ResourceId FROM ControlProperty WHERE NodeId = @SourceID  AND ( IsBeingInserted = 0 AND IsBeingUpdated = 0 )
		end
		if ( @Emptytables & 8 ) = 0
		begin
			INSERT INTO NodeLayout (IsBeingInserted, IsBeingUpdated, IsBeingDeleted, NodeId, Id, Name, LayoutTemplateGUID) 
				SELECT 0, 0, 0, @DestID, Id, NewName, NewLayoutTemplateGUID FROM NodeLayout WHERE NodeId = @SourceID AND IsBeingDeleted = 0 and ( isBeingInserted = 1 or isbeingupdated = 1 )
			INSERT INTO NodeLayout (IsBeingInserted, IsBeingUpdated, IsBeingDeleted, NodeId, Id, Name, LayoutTemplateGUID) 
				SELECT 0, 0, 0, @DestID, Id, Name, LayoutTemplateGUID FROM NodeLayout WHERE NodeId = @SourceID  AND ( IsBeingInserted = 0 AND IsBeingUpdated = 0 )
		end
		if ( @EmptyTables & 64 ) = 0	
		begin
			INSERT INTO LayoutProperty (IsBeingInserted, IsBeingUpdated, IsBeingDeleted, NodeId, LayoutId, Id, PropName, IsControl, PropValue, ControlId) 
				SELECT 0, 0, 0, @DestID, LayoutId, Id, NewPropName, NewIsControl, NewPropValue, NewControlId FROM LayoutProperty WHERE NodeId = @SourceID AND IsBeingDeleted = 0 and ( isBeingInserted = 1 or isbeingupdated = 1 )
			INSERT INTO LayoutProperty (IsBeingInserted, IsBeingUpdated, IsBeingDeleted, NodeId, LayoutId, Id, PropName, IsControl, PropValue, ControlId) 
				SELECT 0, 0, 0, @DestID, LayoutId, Id, PropName, IsControl, PropValue, ControlId FROM LayoutProperty WHERE NodeId = @SourceID AND ( IsBeingInserted = 0 AND IsBeingUpdated = 0 )
		end
	end
	/* All the rest */
	if ( @EmptyTables & 2 ) = 0
	begin
		INSERT INTO NodeProperty (IsBeingInserted, IsBeingUpdated, IsBeingDeleted, NodeId, Id, 
									PropName, PropValue, PropValueLong, PropType) 
			SELECT 0, 0, 0, @DestID, Id, PropName, NewPropValue, NewPropValueLong, PropType
			FROM NodeProperty WHERE NodeId = @SourceID AND IsBeingDeleted = 0  and ( isBeingInserted = 1 or isbeingupdated = 1 )

		INSERT INTO NodeProperty (IsBeingInserted, IsBeingUpdated, IsBeingDeleted, NodeId, Id, PropName, 
								PropValue, PropValueLong, PropType) 
			SELECT 0, 0, 0, @DestID, Id, PropName, PropValue, PropValueLong, PropType
			FROM NodeProperty WHERE NodeId = @SourceID AND ( IsBeingInserted = 0 AND IsBeingUpdated = 0 )

	end
	if ( @EmptyTables & 1 ) = 0
	begin
		INSERT INTO NodeRole (IsBeingInserted, IsBeingUpdated, IsBeingDeleted, NodeId, RoleGUID) 
			SELECT 0, 0, 0, @DestID, RoleGUID FROM NodeRole WHERE NodeId = @SourceID AND IsBeingDeleted = 0
	end
END
ELSE
BEGIN

	/* Layout Templates */
	if @nodeType = 0x10000
	begin
		INSERT INTO LayoutTemplateProperty (IsBeingInserted, IsBeingUpdated, IsBeingDeleted, NodeId, Id, PropName, PropValue, IsControl, ControlId)
			SELECT 0, 0, 0, @DestID, Id, PropName, PropValue, IsControl, ControlId FROM LayoutTemplateProperty WHERE NodeId = @SourceID AND IsBeingInserted = 0
	end

	/* User role */
	if @nodeType = 0x100000
	begin
		INSERT INTO UserRoleMember (IsBeingInserted, IsBeingUpdated, IsBeingDeleted, NodeId, UserId)
			SELECT 0, 0, 0, @DestID, UserId FROM UserRoleMember WHERE NodeId = @SourceID AND IsBeingInserted = 0
		if ( @EmptyTables & 128 ) = 0
			INSERT INTO UserRoleNode (IsBeingInserted, IsBeingUpdated, IsBeingDeleted, NodeId, NodeType, Rights)
				SELECT 0, 0, 0, @DestID, NodeType, Rights FROM UserRoleNode WHERE NodeId = @SourceID AND IsBeingInserted = 0  
	end
	
	/*if @nodeType = 0x00000400 or @nodeType = 0x80000800 or @nodeType = 0x00001000
		INSERT INTO GalleryControl (IsBeingInserted, IsBeingUpdated, IsBeingDeleted, NodeId, Id, ActiveXGUID, Codebase, Name, Platform, Version)
			SELECT 0, 0, 0, @DestID, Id, ActiveXGUID, CodeBase, Name, Platform, Version FROM GalleryControl WHERE NodeId = @SourceID AND IsBeingInserted = 0
	*/

	/* If we are not a shortcut... */
	if @nodetype <> 0x80000020
	begin
		if ( @EmptyTables & 16 ) = 0
			INSERT INTO NodeResource (IsBeingInserted, IsBeingUpdated, IsBeingDeleted, NodeId, Id, Name, IsLink, URL, ResourceBlobId, NodeGUID, LinkAnchor) 
				SELECT 0, 0, 0, @DestID, Id, Name, IsLink, URL, ResourceBlobId, NodeGUID, LinkAnchor FROM NodeResource WHERE NodeId = @SourceID AND IsBeingInserted = 0			
		if ( @EmptyTables & 32 ) = 0
			INSERT INTO NodeControl (IsBeingInserted, IsBeingUpdated, IsBeingDeleted, NodeId, Id, Name, ControlGUID) 
				SELECT 0, 0, 0, @DestID, Id, Name, ControlGUID FROM NodeControl WHERE NodeId = @SourceID AND IsBeingInserted = 0
		if ( @EmptyTables & 4 ) = 0
			INSERT INTO ControlProperty (IsBeingInserted, IsBeingUpdated, IsBeingDeleted, NodeId, ControlId, Id, PropName, IsResource, PropValue, ResourceId) 
				SELECT 0, 0, 0, @DestID, ControlId, Id, PropName, IsResource, PropValue, ResourceId FROM ControlProperty WHERE NodeId = @SourceID AND IsBeingInserted = 0
		if ( @EmptyTables & 8 ) = 0
			INSERT INTO NodeLayout (IsBeingInserted, IsBeingUpdated, IsBeingDeleted, NodeId, Id, Name, LayoutTemplateGUID) 
				SELECT 0, 0, 0, @DestID, Id, Name, LayoutTemplateGUID FROM NodeLayout WHERE NodeId = @SourceID AND IsBeingInserted = 0
		if ( @EmptyTables & 64 ) = 0
			INSERT INTO LayoutProperty (IsBeingInserted, IsBeingUpdated, IsBeingDeleted, NodeId, LayoutId, Id, PropName, IsControl, PropValue, ControlId) 
				SELECT 0, 0, 0, @DestID, LayoutId, Id, PropName, IsControl, PropValue, ControlId FROM LayoutProperty WHERE NodeId = @SourceID AND IsBeingInserted = 0
	end

	if ( @EmptyTables & 2 ) = 0
		INSERT INTO NodeProperty (IsBeingInserted, IsBeingUpdated, IsBeingDeleted, NodeId, Id, PropName, 
								PropValue, PropValueLong, propType) 
			SELECT 0, 0, 0, @DestID, Id, PropName, PropValue, PropValueLong, PropType
			FROM NodeProperty WHERE NodeId = @SourceID AND IsBeingInserted = 0
	if ( @EmptyTables & 1 ) = 0
		INSERT INTO NodeRole (IsBeingInserted, IsBeingUpdated, IsBeingDeleted, NodeId, RoleGUID) 
			SELECT 0, 0, 0, @DestID, RoleGUID FROM NodeRole WHERE NodeId = @SourceID AND IsBeingInserted = 0
	
END

GO

create PROCEDURE CopyAssocDataToOtherVersion
	( 
	@SourceID int 
	)
	with encryption
AS

set nocount on

DECLARE @DestID int, @NodeType int

select @DestID = node1.id, @NodeType = node1.Type FROM node node1, node node2 where Node1.nodeguid = node2.nodeguid and node1.id <> node2.id and node2.id = @sourceID

Exec CopyAssociatedData @sourceID, @DestID, 0, @NodeType, 0


GO

create PROCEDURE GetUserID
	(
	@UserName		nvarchar(450), 
	@SID			varchar(255),
	@Alias			nvarchar(450),	
	@AddIfNotFound	tinyint,
	@UserId 		int OUTPUT
	)
	with encryption
as
	set nocount on
	set @UserId = 0
	
	declare @UpdateSID tinyint
	declare @ExistingSID varchar(255)
	set @UpdateSID = 0

	/*first search by username*/
	SELECT @UserId = userid, @ExistingSID = UserSID FROM aeuser WHERE usernameupper = UPPER(@UserName)

	/*see if we can use SID to find the user*/
	if isnull(@UserId, 0) = 0  and  isnull(@SID, '') <> ''
		SELECT @UserId = userid, @ExistingSID = UserSID FROM aeuser WHERE UserSID is not null and UserSID = @SID 

	/*see if we can use Alias*/
	if isnull(@UserId, 0) = 0 and isnull(@Alias, '') <> ''
		SELECT @UserId = userid, @ExistingSID = UserSID FROM aeuser WHERE usernameupper = UPPER(@Alias)

	if isnull(@UserId, 0) = 0
	begin
		if @AddIfNotFound = 1
		begin
			INSERT INTO aeuser (username, UserSID) VALUES (@UserName, nullif(@SID, ''))
			set @UserId = @@IDENTITY
		end
		else
			set @UserId = isnull(@UserId, 0)
	end
	else	--update SID if it's available
		if isnull(@ExistingSID, '') = '' and isnull(@SID, '') <> ''
			update AEUser set UserSID = @SID where UserId = @UserId	




GO


create procedure GetDescendants
	(
	@ParentGUID char(38),
    @GetCheckedOutVersion int,
	@UserGroupIDs varchar(1000)
	)
	with encryption
as
   declare @level int, @currParentGUID char(38), @currID int
   declare @SQL nvarchar(1000), @TempTableCreated int
   declare @IsAdministrator int

   select @IsAdministrator = 0
   select @TempTableCreated = 0

   set nocount on

   /*first check if the user is an administrator*/
   --exec CheckAdminRights @UserGroupIds, @IsAdministrator out
	if @UserGroupIDs = '-1'
		set @IsAdministrator = 1

 
   if @IsAdministrator = 0 
 	   begin
		create table #Node_temp
		(
		NodeGUID char(38) not null,
		ParentGUID char(38),
		Id int not null,
		Type int not null
		)
		select @TempTableCreated = 1;

	    /*retrieve nodes that the user has rights to read into a temporary table*/
       	set @SQL = 'insert into #Node_temp (NodeGUID, ParentGUID, ID, Type) ' + 
		'select n1.NodeGUID, n1.ParentGUID, n1.Id, n1.Type '  + 
		'from node as n1, noderole as nr, node as UserRole, UserRoleNode as urn, UserRoleMember as urm ' +
		'where n1.id = nr.nodeid and nr.RoleGuid = UserRole.NodeGuid and '+ 
		'UserRole.ID = urn.NodeID and urn.NodeType = n1.Type '+ 
		'and urn.NodeId = urm.NodeID AND urn.Rights%2 > 0 ' +
		' and urm.UserId IN (' + @UserGroupIDs + ') and ' +
		'(n1.ischeckedoutversion = convert(tinyint, @GetCheckedOutVersion) or '+
		'(n1.ischeckedoutversion != convert(tinyint, @GetCheckedOutVersion) and not exists (select id from Node as n2 '+
		'where n2.ischeckedoutversion = convert(tinyint, @GetCheckedOutVersion) and n2.nodeguid = n1.nodeguid)))'
 
   		exec sp_executesql @SQL, N'@GetCheckedOutVersion int', @GetCheckedOutVersion

   		CREATE Index Node_temp_nodeguid On #Node_temp
		(
		nodeguid
		)

	   end

    /*now create a temporary table that will store expanded hierarchy*/
    create table #node_result
		(
		seq_no	int IDENTITY,
		NodeLevel int not null,
		Id int not null
		);

    /*create another temporary table that will serve as a stack*/
   create table #node_stack
		(
		NodeLevel int not null,
		NodeGUID char(38) not null,
		Id int not null
		);    	

   select @currID = 0
   if @IsAdministrator > 0 
	select @level = 1, @currParentGuid = NodeGUID, @currID = Id from Node where NodeGUID = @ParentGUID
   else
	select @level = 1, @currParentGuid = NodeGUID, @currID = Id from #Node_temp where NodeGUID = @ParentGUID

   if @@ROWCOUNT > 0 
      begin
	insert into #node_stack values (@level, @currParentGUID, @currID)

	while (@level > 0 )	--Do if any levels remain
	   begin
		--see if there are any rows for the level we're on
		if exists( select * from #node_stack where NodeLevel = @level )
		   begin
			--get first node at current level
			set rowcount 1
			select @currParentGUID = NodeGUID, @currID = Id from #node_stack where NodeLevel = @level
			set rowcount 0

			--insert this node into result table
			insert into #node_result (NodeLevel, ID) values (@level, @currID)
			
			--delete this node from the stack
			delete from #node_stack where NodeLevel = @level and id = @currID

			--get children of this node
			if @IsAdministrator > 0 
			   begin
				insert into #node_stack select @level + 1, n1.NodeGUID, n1.Id from Node n1
				where parentguid = @currParentGUID and 
				(n1.ischeckedoutversion = @GetCheckedOutVersion or 
		  		(n1.ischeckedoutversion != @GetCheckedOutVersion 
				and not exists (select id from Node as n2 
				where n2.ischeckedoutversion = @GetCheckedOutVersion and n2.nodeguid = n1.nodeguid)))
				if @@ROWCOUNT > 0
				   select @level = @level + 1
			   end
			else
			   begin
				insert into #node_stack select @level + 1, NodeGUID, Id from #Node_temp
				where parentguid = @currParentGUID
				if @@ROWCOUNT > 0
				   select @level = @level + 1
			   end
		     end
		else
		   --there is no level below this one, so pop up one
		   select @level = @level - 1
	   end	
      end
    else
	begin
		select @SQL = 'Node with GUID ' + @ParentGUID + ' does not exist in the database.'
		raiserror (@SQL, 15, 1)
		goto Cleanup
	end

   --now just join node table to the #node_result   
   select tmp.NodeLevel as [Level], n.NodeGUID, n.ParentGUID, n.Id, n.Type, 
	n.IsShortcut, n.name from
	node as n join #node_result as tmp on (n.id = tmp.id)
	order by tmp.seq_no

Cleanup:
   drop table #node_stack, #node_result

   if @TempTableCreated > 0
	drop table #node_temp

go   

create procedure DeleteRecursive
	(
	@DeleteAction int,	
	@SourceNodeGUID char(38),
	@NewParentGUID char(38),
	@UserID	int,
	@UserMachineName nvarchar(50),
	@UserGroupIDs varchar(1000),
	@ArchiveFolderGUID char(38)
	)
	with encryption
as
/*Enum values for @DeleteAction
	0 - Move to Recycle Bin
	1 - Move from Recycle Bin
	2 - Purge from the database
*/

/*Enum values for Node Status
	2 - USER_LOCK_FAILED
	4 - RIGHTS_FAILED
	8 - TEMPLATE_FAILED
*/
	declare @MOVE_TO_RECYCLE_BIN int, @MOVE_FROM_RECYCLE_BIN int, @PURGE int
	set @MOVE_TO_RECYCLE_BIN = 0
	set @MOVE_FROM_RECYCLE_BIN = 1
	set @PURGE = 2

	declare @ACL_FULL_CONTROL varchar(15), @ACL_DELETE_SIMPLE_NODE varchar(15), @ACL_DELETE_OWN_NODE varchar(15)
	set @ACL_FULL_CONTROL = '-2147483648'
	set @ACL_DELETE_SIMPLE_NODE = '16'
	set @ACL_DELETE_OWN_NODE = '67108864'

   declare @level int, @currParentGUID char(38), @currID int, @Type int, @name varchar(250), @IsShortcut tinyint
   declare @Failed tinyint
   declare @SQL nvarchar(1000)
   declare @HasRights int
   declare @IsItem tinyint
   DECLARE @dateGMT datetime, @dateCurrent datetime
   declare @IsSourceNodeDeleted int
  
   set nocount on
   set @Failed = 0
   set @IsItem = 0
   set @Type = 0
   set @HasRights = 0

   /*temporary table that stores nodes being deleted, 
	that is the specified node plus all its children*/
    create table #nodes_to_delete
		(
		seq_no	int IDENTITY,
		NodeLevel int not null,
		Id int not null,
		NodeGUID char(38) not null,
		Type int not null,
		IsShortcut tinyint not null,
		name varchar(250) null
		);

    CREATE Index nodes_to_delete_id On #nodes_to_delete
		(
		id
		)

   /* temporary table that stores node status info*/
   create table #nodes_delete_info
		(
		NodeGUID char(38) not null,
		NodeState int default 0,
		NodeLevel int NULL,
		LockingUser int,
		LockingMachine nvarchar(50),
		name varchar(250) null
		)

    CREATE Index nodes_delete_info_nodeguid On #nodes_delete_info
		(
		nodeguid
		)

    /*another temporary table that serves as a stack when expanding tree hierarchy*/
   create table #node_stack
		(
		NodeLevel int not null,
		Id int not null,	
		NodeGUID char(38) not null,
		Type int not null,
		IsShortcut tinyint,
		name varchar(250) null
		);    	

    CREATE Index node_stack_level On #node_stack
		(
		NodeLevel
		)

	/*find out whether we're deleting a single node or a container*/
	set rowcount 1	
	select @Type = Type, @IsSourceNodeDeleted = DeletedByUserID 
	from node 
	where NodeGUID = @SourceNodeGUID

	if @Type = 0 
	begin
		select @SQL = 'Node with GUID ' + @SourceNodeGUID + ' does not exist in the database.'
		raiserror (@SQL, 15, 1)
		goto Cleanup
	end
	else
		if @Type != 4 and @Type != 64 and @Type != 1024 and 	/*these are all container types*/
		@Type != 16384 and @Type != 524288 and @Type != 2097152
			select @IsItem = 1	

	set rowcount 0

   /*retrieve all children of the specified node*/
	if @IsItem > 0 	/*single node case*/
   	begin
	    insert into #nodes_to_delete (Nodelevel, id, NodeGUID, Type, IsShortcut, name)
		select 0, id, NodeGUID, Type, IsShortcut, name 
		from node 
		where NodeGUID = @SourceNodeGUID
	end

	else	/*container*/

	begin	
		select @level = 1, @currParentGuid = NodeGUID, @currID = Id, @Type = Type, 
		@IsShortcut = IsShortcut, @name = name  
		from Node with (UPDLOCK)
		where NodeGUID = @SourceNodeGUID

		insert into #node_stack (NodeLevel, Id, NodeGUID, Type, IsShortcut, name ) 
		values (@level, @currID, @currParentGUID, @Type, @IsShortcut, @name )

		while (@level > 0 )	--Do if any levels remain
		   begin
			--see if there are any rows for the level we're on
			if exists( select Id from #node_stack where NodeLevel = @level )
			   begin
				--get first node at current level
				set rowcount 1
				select @currParentGUID = NodeGUID, @currID = Id, @Type = Type, 
				@IsShortcut = IsShortcut, @name = name
				from #node_stack where NodeLevel = @level
				
				set rowcount 0
	
				--insert this node into result table
				insert into #nodes_to_delete (NodeLevel, Id, NodeGUID, Type, IsShortcut, name )
					values (@level, @currID, @currParentGUID, @Type, @IsShortcut, @name )
				
				--delete this node from the stack
				delete from #node_stack 
				where NodeLevel = @level and id = @currID
	
				--get children of this node
				insert into #node_stack (NodeLevel, Id, NodeGUID, Type, IsShortcut, name )
					select @level + 1, n1.Id, n1.NodeGUID, n1.Type, n1.IsShortcut, n1.name 
					from Node n1 with (UPDLOCK)
					where parentguid = @currParentGUID 
				if @@ROWCOUNT > 0
				   select @level = @level + 1
			    end
			else
			   --there is no level below this one, so pop up one
			   select @level = @level - 1
		  end	--while (@level > 0 )
     end	--container

    /*OK, at this point we have descendants in the #nodes_to_delete table. 
    Now we want retrieve all shortcuts to the pages we're deleting */    
    insert into #nodes_to_delete (Nodelevel, id, NodeGUID, Type, IsShortcut, name)
		select nd.NodeLevel, Posting.Id, Posting.NodeGUID, Posting.Type, Posting.IsShortcut, Page.name 
		from #nodes_to_delete nd, node Posting with (UPDLOCK), node Page 
		where Posting.FollowGUID = nd.NodeGUID
		and Page.NodeGUID = Posting.FollowGUID

    /*populate delete_info table*/
    insert into #nodes_delete_info (NodeGUID, nodelevel, name) 
		select distinct nd.NodeGUID, nd.nodelevel, nd.name  
		from #nodes_to_delete nd 
		order by nd.nodelevel

	if @DeleteAction != @MOVE_FROM_RECYCLE_BIN /*if we're moving to recycle bin or purging*/
	begin

	    /*find nodes that are locked and check if the user is the one who locked them*/
	    update #nodes_delete_info 
		set nodestate = nodestate | 2,  /*USER_LOCK_FAILED*/
		LockingUser = n.LockUserId, LockingMachine = n.LockMachinename
		from #nodes_to_delete nd inner join  node n on (nd.id = n.id)
		where #nodes_delete_info.Nodeguid = n.nodeguid and n.IsLocked = 1 and 
		(n.LockUserId != @UserID or upper(n.LockMachinename) != upper(@UserMachineName))
	
	    if @@ROWCOUNT > 0 
		select @Failed = 1

	    /*if we're deleting templates make sure there are no pages or channels that depend on them*/
	    update #nodes_delete_info set nodestate = nodestate | 8  /*TEMPLATE_FAILED*/
		from NodeLayout, node 
		where #nodes_delete_info.NodeGUID = NodeLayout.LayoutTemplateGUID
		and NodeLayout.NodeID = node.ID and node.ArchiveSourceGUID is null /*do not check archived versions*/
	
	    if @@ROWCOUNT > 0 
		select @Failed = 1
	end

    /*check if user has rights to delete/undelete these nodes using the following logic:
	- currently we require a user to be an administrator in order delete/restore containers
    (this check is actually done in the server code, but we're double checking just in case)
	- if a user is purging or restoring an item (page, template, etc.) and he is not an administrator 
	then we check if he is the same user who originally deleted this item (that is moved to Recycle Bin)
	- things get really ugly when we're moving an item to Recycle Bin as at this time we have to perform 
	a horrible join to check user's rights to this item
	*/
	
	--let's hope that this guy is an administrator
	--exec CheckAdminRights @UserGroupIds, @HasRights out
	/*we used to check administrator's rights within the stored procedure, but this didn't account for local 
	administrators, so now we're passing this info in @UserGroupIDs */
	if @UserGroupIDs = '-1'
		set @HasRights = 1

	if @HasRights = 0 
	begin
		--we're dealing with a single node here
		if @IsItem > 0
			begin
				declare @everybody int
				select @everybody = userid 
				from aeuser 
				where usernameupper = 'EVERYBODY' 

				-- we're purging or restoring an item
				if @DeleteAction = @MOVE_FROM_RECYCLE_BIN or
					(@DeleteAction = @PURGE and @IsSourceNodeDeleted is not null 
					and @IsSourceNodeDeleted > 0) --the item is already in Recycle Bin
					begin
						set @SQL = 'update #nodes_delete_info set nodestate = nodestate | 4 ' +  /*RIGHTS_FAILED*/ 
						'where #nodes_delete_info.Nodeguid not in ' + 
						'(select n.NodeGUID from #nodes_to_delete nd inner join  node n on (nd.id = n.id) ' + 
						'where n.DeletedByUserId = ' + convert(varchar(10), @UserID)  +
						' or n.OwnerID IN (' + @UserGroupIDs + ') or n.ownerid = ' +
						convert(varchar(10),@everybody) + ')'

						exec sp_executesql @SQL
					
					    if @@ROWCOUNT > 0 
							select @Failed = 1
					end
				else	--moving an item into Recycle Bin or purging from WBC
					begin
						--now let's do that horrible join...
					    set @SQL = 'update #nodes_delete_info set nodestate = nodestate | 4 ' +   /*RIGHTS_FAILED*/ 
						'where NodeGUID not in (select n1.NodeGUID ' +  
						'from #nodes_to_delete nd, node n1, node NodeParent, noderole as nr, node as UserRole, ' + 
						'UserRoleNode as urn, UserRoleMember as urm ' +  
						'where nd.id = n1.id and n1.ParentGUID = NodeParent.NodeGUID and ' +  
						'NodeParent.id = nr.nodeid and nr.RoleGuid = UserRole.NodeGuid and ' + 
						'UserRole.ID = urn.NodeID and urn.NodeType = ' + 
						'case when n1.Type = 16 and n1.IsShortcut > 0 then -2147483616 else n1.Type end and ' + 
						'urn.NodeId = urm.NodeID and ' + 
						'urm.UserId IN (' + @UserGroupIDs + ') ' + 
						' AND (urn.Rights = ' + @ACL_FULL_CONTROL + 
						' or floor(urn.Rights/' + @ACL_DELETE_SIMPLE_NODE + ')%2 > 0 or ' + 
						'(floor(urn.Rights/' + @ACL_DELETE_OWN_NODE + ')%2 > 0 and ' + 
						'( n1.OwnerID IN (' + @UserGroupIDs + ') or n1.ownerid = ' + 
						convert(varchar(10),@everybody) + '))) )'

						exec sp_executesql @SQL
				    	
						if @@ROWCOUNT > 0 
							select @Failed = 1
					end
			end
		else	--containers and their children, need administrator's rights
			begin
			   update #nodes_delete_info set nodestate = nodestate | 4  /*RIGHTS_FAILED*/ 
			   select @Failed = 1
			end	
	end


   	if @Failed = 0 
	begin
		if @DeleteAction = @MOVE_TO_RECYCLE_BIN	/*moving into recycle bin*/
			begin
				select @dateCurrent = GETDATE()
				
				exec GetGMTTime @dateCurrent, @dateGMT out

				update node set DeletedByUserId = @UserID, DeletedWhen = @dateGMT ,
					ApprovalStatus = 0
					from #nodes_to_delete 
					WHERE Node.Id = #nodes_to_delete.id

				update nodelock set newApprovalStatus = 0
					from #nodes_to_delete 
					WHERE Nodelock.nodeId = #nodes_to_delete.id
				
				update node set IsCheckedOut = 1, IsCheckedOutVersion = 1, HasCheckedInVersion = 0
					from #nodes_to_delete 
					where Node.Id = #nodes_to_delete.id and 
					Node.Type in (16, 65536) and Node.IsCheckedOut = 0

				update node set ParentGUID = @NewParentGUID 
				where NodeGUID = @SourceNodeGUID
			end
		else if @DeleteAction = @MOVE_FROM_RECYCLE_BIN	/*Moving from recycle bin*/
			begin
				update node set DeletedByUserId = NULL, DeletedWhen = NULL
					from #nodes_to_delete 
					WHERE Node.Id = #nodes_to_delete.id

				update node set ParentGUID = @NewParentGUID 
				where NodeGUID = @SourceNodeGUID
			end

		else 		/*@DeleteAction = 2 - purging*/
		 	begin
			/*now it's time to delete these nodes*/
			DELETE NodeProperty FROM #nodes_to_delete WHERE NodeProperty.NodeId = #nodes_to_delete.id and #nodes_to_delete.Type != 256
			DELETE NodeRole from #nodes_to_delete WHERE NodeRole.NodeId = #nodes_to_delete.id and #nodes_to_delete.Type != 256
			DELETE NodeResource from #nodes_to_delete WHERE NodeResource.NodeId = #nodes_to_delete.id and #nodes_to_delete.Type != 256
			DELETE ControlProperty from #nodes_to_delete WHERE ControlProperty.NodeId = #nodes_to_delete.id and #nodes_to_delete.Type != 256
			DELETE NodeControl from #nodes_to_delete WHERE NodeControl.NodeId = #nodes_to_delete.id and #nodes_to_delete.Type != 256
			DELETE LayoutTemplateProperty from #nodes_to_delete WHERE LayoutTemplateProperty.NodeId = #nodes_to_delete.id and #nodes_to_delete.Type != 256
			DELETE LayoutProperty from #nodes_to_delete WHERE LayoutProperty.NodeId = #nodes_to_delete.id and #nodes_to_delete.Type != 256
			DELETE NodeLayout from #nodes_to_delete WHERE NodeLayout.NodeId = #nodes_to_delete.id and #nodes_to_delete.Type != 256
			DELETE GalleryControl from #nodes_to_delete WHERE GalleryControl.NodeId = #nodes_to_delete.id and #nodes_to_delete.Type != 256
			DELETE UserRoleMember from #nodes_to_delete WHERE UserRoleMember.NodeId = #nodes_to_delete.id and #nodes_to_delete.Type != 256
			DELETE UserRoleNode from #nodes_to_delete WHERE UserRoleNode.NodeId = #nodes_to_delete.id and #nodes_to_delete.Type != 256
			DELETE NodeLock from #nodes_to_delete WHERE NodeLock.NodeId = #nodes_to_delete.id and #nodes_to_delete.Type != 256
		
			DELETE Node from #nodes_to_delete WHERE Node.Id = #nodes_to_delete.id and #nodes_to_delete.Type != 256
		
			/*delete revisions*/
			DELETE NodeProperty where nodeid in (select node.id from node, #nodes_to_delete 
											WHERE #nodes_to_delete.NodeGUID = node.ArchiveSourceGUID
											and #nodes_to_delete.Type != 256)
			DELETE NodeRole where nodeid in (select node.id from node, #nodes_to_delete 
											WHERE #nodes_to_delete.NodeGUID = node.ArchiveSourceGUID
											and #nodes_to_delete.Type != 256)
			DELETE NodeResource where nodeid in (select node.id from node, #nodes_to_delete 
											WHERE #nodes_to_delete.NodeGUID = node.ArchiveSourceGUID
											and #nodes_to_delete.Type != 256) 
			DELETE ControlProperty where nodeid in (select node.id from node, #nodes_to_delete 
											WHERE #nodes_to_delete.NodeGUID = node.ArchiveSourceGUID
											and #nodes_to_delete.Type != 256)
			DELETE NodeControl where nodeid in (select node.id from node, #nodes_to_delete 
											WHERE #nodes_to_delete.NodeGUID = node.ArchiveSourceGUID
											and #nodes_to_delete.Type != 256)

			DELETE LayoutTemplateProperty where nodeid in (select node.id from node, #nodes_to_delete 
											WHERE #nodes_to_delete.NodeGUID = node.ArchiveSourceGUID
											and #nodes_to_delete.Type != 256)
 
			DELETE LayoutProperty where nodeid in (select node.id from node, #nodes_to_delete 
											WHERE #nodes_to_delete.NodeGUID = node.ArchiveSourceGUID
											and #nodes_to_delete.Type != 256)
			DELETE NodeLayout where nodeid in (select node.id from node, #nodes_to_delete 
											WHERE #nodes_to_delete.NodeGUID = node.ArchiveSourceGUID
											and #nodes_to_delete.Type != 256)
			DELETE GalleryControl where nodeid in (select node.id from node, #nodes_to_delete 
											WHERE #nodes_to_delete.NodeGUID = node.ArchiveSourceGUID
											and #nodes_to_delete.Type != 256)
			DELETE UserRoleMember where nodeid in (select node.id from node, #nodes_to_delete 
											WHERE #nodes_to_delete.NodeGUID = node.ArchiveSourceGUID
											and #nodes_to_delete.Type != 256)
			DELETE UserRoleNode where nodeid in (select node.id from node, #nodes_to_delete 
											WHERE #nodes_to_delete.NodeGUID = node.ArchiveSourceGUID
											and #nodes_to_delete.Type != 256) 

			DELETE NodeLock 
			from #nodes_to_delete, Node 
			WHERE #nodes_to_delete.NodeGUID = Node.ArchiveSourceGUID
			and NodeLock.NodeId = Node.id and #nodes_to_delete.Type != 256

			DELETE Node 
			FROM #Nodes_to_delete 
			WHERE ArchiveSourceGUID = #nodes_to_delete.NodeGUID and #nodes_to_delete.Type != 256
			
			select @dateCurrent = GETDATE()
			exec GetGMTTime @dateCurrent, @dateGMT out
	
			--move resource galery items to Archive Folder
			update node set ParentGUID = @ArchiveFolderGUID,
			ArchivedWhen = @dateGMT,
			IsLocked = 0	
			from #nodes_to_delete
			where #nodes_to_delete.Id = Node.Id and #nodes_to_delete.Type = 256

			--clear the lock on resource galery items 
			DELETE NodeLock 
			from #nodes_to_delete, Node 
			where #nodes_to_delete.Id = Node.Id and #nodes_to_delete.Type = 256 and NodeLock.NodeId = Node.Id
			end			
			
	    /*return node status into*/	
    	exec sp_executesql N'select ndi.NodeGUID, ndi.NodeState, nd.Type, nd.IsShortcut, nd.name,
		ndi.LockingUser, ndi.LockingMachine, #Sandbox.LastChangeTime
		from (#nodes_delete_info ndi left join 
		(select distinct nodeguid, Type, IsShortcut, name from #nodes_to_delete) as nd 
		on ndi.nodeguid = nd.nodeguid) left join #Sandbox on ndi.NodeGUID = #Sandbox.NodeGUID'
	end
	else
	    /*return node status into*/	
    	exec sp_executesql N'select distinct ndi.NodeGUID, ndi.NodeState, n.Type, n.IsShortcut, ndi.name,
		ndi.LockingUser, ndi.LockingMachine, max(n.LastChangeTime) as LastChangeTime
		from #nodes_delete_info ndi left join node as n
		on ndi.nodeguid = n.nodeguid 
		where ndi.NodeState > 0
		group by ndi.NodeGUID, ndi.NodeState, n.Type, n.IsShortcut, ndi.name,
		ndi.LockingUser, ndi.LockingMachine'


Cleanup:

   /*we're done, clean up temp tables*/
   drop table #nodes_to_delete
   drop table #nodes_delete_info
   drop table #node_stack


GO


create procedure LockNode
	(
	@NodeId int,
	@LockUserId int,
	@LockMachineName nvarchar(50)
	) 
	with encryption
as

	set nocount on

	UPDATE Node SET IsLocked = 1, LockMachinename = @LockMachineName,
			LockUserId = @LockUserId WHERE Id = @NodeId

	INSERT INTO NodeLock 
		(NodeId, NewIsHighPriority, NewSameRightsAsParent, 
		NewReadyForApproval, NewSortOrdinal, NewApprovalStatus, 
		NewApprovedByUserId, NewOwnerId, NewEffectiveDate, NewExpiryDate, 
		NewTemplateGUID, NewControlType, NewEmitterGUID, 
		NewRuntimeControlGUID, NewDesigntimeControlGUID, NewName, 
		NewTemplateType, NewDirectoryService, NewDirectoryGUID,
		NewIsHiddenModePublished, NewIsRobotFollowable, NewIsRobotIndexable, NewDisplayName)
	select @NodeID, IsHighPriority, SameRightsAsParent, 
		ReadyForApproval, SortOrdinal, ApprovalStatus, 
		ApprovedByUserId, OwnerId, EffectiveDate, ExpiryDate, 
		TemplateGUID, ControlType, EmitterGUID, 
		RuntimeControlGUID, DesigntimeControlGUID, Name, 
		TemplateType, DirectoryService, DirectoryGUID, IsHiddenModePublished, IsRobotFollowable, IsRobotIndexable,
		DisplayName
	from Node
	where id = @NodeID

go

create procedure RollbackLock
        ( 
        @NodeID int, 
        @NodeType int, 
        @EmptyTables int 
        ) 
	with encryption
as 

/* These are the values for empty table bitfields: 
NO_ROLE_ACL = 1,        NodeRole 
NO_GENERIC_PROPS = 2,   NodeProperty 
NO_CONTROL_ITEMS = 4,   ControlProp 
NO_LAYOUTS = 8,         NodeLayout 
NO_RESOURCES = 16,      NodeResource 
NO_CONTROLS = 32,       NodeControl 
NO_LAYOUT_PROPS = 64,   LayoutProp 
NO_ROLE_RIGHTS = 128,   UserRoleNode 
*/ 
        set nocount on 

        /* 
         * delete the entries that are being inserted. 
         */ 

        /* Layout Templates */ 
        if @nodeType = 0x10000 
        begin 
                DELETE FROM LayoutTemplateProperty WHERE NodeId = @NodeId and IsBeingInserted = 1 
        end 

        /* User role */ 
        if @nodeType = 0x100000 
        begin 
                DELETE FROM UserRoleMember WHERE NodeId = @NodeId and IsBeingInserted = 1 
                if ( @EmptyTables & 128 ) = 0 
                        DELETE FROM UserRoleNode WHERE NodeId = @NodeId and IsBeingInserted = 1 
        end 
        
        /*if @nodeType = 0x00000400 or @nodeType = 0x80000800 or @nodeType = 0x00001000 
                DELETE FROM GalleryControl WHERE NodeId = @NodeId and IsBeingInserted = 1 
        */ 

        /* If we are not a shortcut... */ 
        if @nodetype <> 0x80000020 
        begin 
                if ( @EmptyTables & 16 ) = 0 
                        DELETE FROM NodeResource WHERE NodeId = @NodeId and IsBeingInserted = 1 
                if ( @EmptyTables & 4 ) = 0 
                        DELETE FROM ControlProperty WHERE NodeId = @NodeId and IsBeingInserted = 1 
                if ( @EmptyTables & 32 ) = 0 
                        DELETE FROM NodeControl WHERE NodeId = @NodeId and IsBeingInserted = 1 
                if ( @EmptyTables & 64 ) = 0 
                        DELETE FROM LayoutProperty WHERE NodeId = @NodeId and IsBeingInserted = 1 
                if ( @EmptyTables & 8 ) = 0 
                        DELETE FROM NodeLayout WHERE NodeId = @NodeId and IsBeingInserted = 1 
        end 
        
        /* Every type of node is concerned with these settings: */ 
        if ( @EmptyTables & 1 ) = 0 
                DELETE FROM NodeRole WHERE NodeId = @NodeId and IsBeingInserted = 1 
        if ( @EmptyTables & 2 ) = 0 
                DELETE FROM NodeProperty WHERE NodeId = @NodeId and IsBeingInserted = 1 



        /* 
         * rollback the data being updated, and undelete objects being deleted. 
         */ 
        
        /* Layout Templates */ 
        if @nodeType = 0x10000 
        begin 
                update LayoutTemplateProperty set isbeingdeleted = 0, isbeingupdated = 0, IsBeingInserted = 0, NewPropName = NULL,

                        NewPropValue = NULL, NewIsControl = NULL, NewControlID = NULL 
                        WHERE NodeId = @NodeId and ( isbeingupdated = 1 or isbeingdeleted = 1 ) 
        end 

        /* User role */ 
        if @nodeType = 0x100000 
        begin 
                if ( @EmptyTables & 128 ) = 0 
                        update UserRoleNode set isbeingdeleted = 0, isbeingupdated = 0, IsBeingInserted = 0, 
                                        NewRights = NULL WHERE NodeId = @NodeId and ( isbeingupdated = 1 or isbeingdeleted = 1 )

                update UserRoleMember set isbeingdeleted = 0, isbeingupdated = 0, IsBeingInserted = 0 
                                WHERE NodeId = @NodeId and ( isbeingupdated = 1 or isbeingdeleted = 1 ) 
        end 

        /*if @nodeType = 0x00000400 or @nodeType = 0x80000800 or @nodeType = 0x00001000 
                update GalleryControl set  isbeingupdated = 0, IsBeingInserted = 0, 
                                NewActiveXGUID = NULL, NewCodebase = NULL, 
                                NewName = NULL, newPlatform = NULL, NewVersion = NULL 
                                WHERE NodeId = @NodeId and  isbeingupdated = 1*/ 
        

        /* If we are not a shortcut... */ 
        if @nodetype <> 0x80000020 
        begin 
                if ( @EmptyTables & 8 ) = 0 
                        update NodeLayout set isbeingdeleted = 0, isbeingupdated = 0, IsBeingInserted = 0, 
                                NewName = NULL, NewLayoutTemplateGUID = NULL 
                                WHERE NodeId = @NodeId and ( isbeingupdated = 1 or isbeingdeleted = 1 ) 

                if ( @EmptyTables & 64 ) = 0 
                        update LayoutProperty set isbeingdeleted = 0, isbeingupdated = 0, IsBeingInserted = 0, 
                                        NewPropName = NULL, NewIsControl = NULL, 
                                        NewPropValue = NULL, NewControlID = NULL 
                                        WHERE NodeId = @NodeId and ( isbeingupdated = 1 or isbeingdeleted = 1 ) 

                if ( @EmptyTables & 32 ) = 0 
                        update NodeControl set isbeingdeleted = 0, isbeingupdated = 0, IsBeingInserted = 0, 
                                NewName = NULL  WHERE NodeId = @NodeId and ( isbeingupdated = 1 or isbeingdeleted = 1 ) 

                if ( @EmptyTables & 4 ) = 0 
                        update ControlProperty set isbeingdeleted = 0, isbeingupdated = 0, IsBeingInserted = 0, 
                                NewPropName = NULL, NewIsResource = NULL, NewPropValue = NULL, 
                                NewResourceID = NULL  WHERE NodeId = @NodeId and ( isbeingupdated = 1 or isbeingdeleted = 1 )

                if ( @EmptyTables & 16 ) = 0 
                        update NodeResource set isbeingdeleted = 0, isbeingupdated = 0, IsBeingInserted = 0, 
                                NewName = NULL, NewIsLink = NULL, NewURL = NULL, 
                                NewResourceBlobID = NULL, NewNodeGUID = NULL, NewLinkAnchor = NULL 
                                WHERE NodeId = @NodeId and ( isbeingupdated = 1 or isbeingdeleted = 0 ) 
        end 

        /* Every type of node is concerned with the following: */ 
        if ( @EmptyTables & 2 ) = 0 
                update NodeProperty set isbeingdeleted = 0, isbeingupdated = 0, IsBeingInserted = 0, 
                        newpropvalue = null, newpropvaluelong = null WHERE NodeId = @NodeId and ( isbeingupdated = 1 or isbeingdeleted = 1 )

        if ( @EmptyTables & 1 ) = 0 
                update NodeRole set isbeingdeleted = 0, isbeingupdated = 0, IsBeingInserted = 0 
                WHERE NodeId = @NodeId and ( isbeingupdated = 1 or isbeingdeleted = 1 ) 



        DELETE FROM NodeLock WHERE NodeId = @NodeID 

        UPDATE Node SET IsLocked = 0, LockMachineName = NULL, LockUserId = NULL 
        WHERE Id = @NodeID 

GO 

create procedure CheckoutNode
	(
	@NodeId int,
	@CheckoutDestructive int,
	@NodeType int,
	@EmptyTables int,
	@IsShortcut int,
	@UserId int
	)
	with encryption

as
	/* These are the values for the empty tables:
	NO_ROLE_ACL = 1,	NodeRole
	NO_GENERIC_PROPS = 2,	NodeProperty
	NO_CONTROL_ITEMS = 4,	ControlProp
	NO_LAYOUTS = 8,		NodeLayout
	NO_RESOURCES = 16,	NodeResource
	NO_CONTROLS = 32,	NodeControl
	NO_LAYOUT_PROPS = 64,	LayoutProp
	NO_ROLE_RIGHTS = 128, 	UserRoleNode
	*/

	set nocount on

	if @CheckoutDestructive > 0
		begin
		UPDATE Node SET CheckedInVersionHasExisted=1, IsCheckedOut=1, 
		IsCheckedOutVersion=1, HasCheckedInVersion=0, 
		ApprovalStatus = 0 WHERE Id = @NodeID
		end
	else
		begin
		if @UserID = 0
			begin
			raiserror ('Invalid user ID.', 15, 1)
			return
			end

		declare @NewNodeID int
		
		insert into Node (IsLocked, IsCheckedOut, IsCheckedOutVersion, HasCheckedInVersion,
					CheckedInVersionHasExisted, IsShortcut, IsBeingInserted, ArchiveState,
					Type, CreatedByUserId, ModifiedByUserId, CreatedWhen, ModifiedWhen,
					LockUserId, LockMachinename, NodeGUID, ParentGUID, OwnerID,
					EffectiveDate, ExpiryDate, IsHighPriority, SameRightsAsParent,
					ReadyForApproval, ApprovalStatus, SortOrdinal, ApprovedByUserId,
					DeletedByUserId, DeletedWhen, Name, FollowGUID,DirectoryService, DirectoryGUID,
					EmitterGUID, RuntimeControlGUID, DesigntimeControlGUID, ControlType,
					Mimetype, ParserGUID, IsBaseTemplate, TemplateType, DerivedFromGUID, TemplateGUID,
					IsHiddenModePublished, IsRobotFollowable, IsRobotIndexable, DisplayName)

		select 0, 1, 1, 1, 1, @IsShortcut, 0, 0, Type, CreatedByUserId, @UserId, CreatedWhen,
			ModifiedWhen, NULL, NULL, NodeGUID, ParentGUID, OwnerId,
			EffectiveDate, ExpiryDate, IsHighPriority, SameRightsAsParent,
			ReadyForApproval, 0, SortOrdinal, NULL,
			DeletedByUserId, DeletedWhen, Name, FollowGUID,DirectoryService, DirectoryGUID,
			EmitterGUID, RuntimeControlGUID, DesigntimeControlGUID, ControlType,
			Mimetype, ParserGUID, IsBaseTemplate, TemplateType, DerivedFromGUID, TemplateGUID,
			IsHiddenModePublished, IsRobotFollowable, IsRobotIndexable, DisplayName
		from node 
		where id = @NodeID
	
		select @NewNodeID = @@IDENTITY
	
		UPDATE node SET IsCheckedOut = 1 where Id = @NodeID
	
		exec CopyAssociatedData @NodeID, @NewNodeID, 0, @NodeType, @EmptyTables
		end
go

create Procedure CommitNode

	(
	@NodeId int,
	@NodeType int,
	@UserID int,
	@KeepLocked tinyint,
	@EmptyTables int
	)
	with encryption

As

	/* These are the values for the empty tables:
	NO_ROLE_ACL = 1,	NodeRole
	NO_GENERIC_PROPS = 2,	NodeProperty
	NO_CONTROL_ITEMS = 4,	ControlProp
	NO_LAYOUTS = 8,		NodeLayout
	NO_RESOURCES = 16,	NodeResource
	NO_CONTROLS = 32,	NodeControl
	NO_LAYOUT_PROPS = 64,	LayoutProp
	NO_ROLE_RIGHTS = 128, 	UserRoleNode
	*/

	/* these are the flags that will help us to determine if assoc data has been deleted, inserted or updated
	RECORDS_INSERTED = 1,	
	RECORDS_UPDATED = 2,	
	RECORDS_DELETED = 4*/

	declare @LayoutTemplatePropertyFlag tinyint
	declare @UserRoleMemberFlag tinyint
	declare @UserRoleNodeFlag tinyint
	declare @NodeResourceFlag tinyint
	declare @ControlPropertyFlag tinyint
	declare @NodeControlFlag tinyint
	declare @LayoutPropertyFlag tinyint
	declare @NodeLayoutFlag tinyint
	declare @NodeRoleFlag tinyint
	declare @NodePropertyFlag tinyint

	set @LayoutTemplatePropertyFlag = 0
	set @UserRoleMemberFlag = 0
	set @UserRoleNodeFlag = 0
	set @NodeResourceFlag = 0
	set @ControlPropertyFlag = 0
	set @NodeControlFlag = 0
	set @LayoutPropertyFlag = 0
	set @NodeLayoutFlag = 0
	set @NodeRoleFlag = 0
	set @NodePropertyFlag = 0

	set nocount on
	
	/* 
	 * delete the entries to be deleted.
	 */

	/* Layout Templates */
	if @nodeType = 0x10000
	begin
		select @LayoutTemplatePropertyFlag =  max(isbeinginserted) |
					case when max(isbeingupdated) > 0 then 2 else 0 end |
					case when max(isbeingdeleted) > 0 then 4 else 0 end  
				 from LayoutTemplateProperty WHERE NodeId = @NodeId

		if( @LayoutTemplatePropertyFlag & 4 ) = 4
			DELETE FROM LayoutTemplateProperty WHERE NodeId = @NodeId and IsBeingDeleted = 1
	end

	/* User role */
	if @nodeType = 0x100000
	begin
		select @UserRoleMemberFlag =  max(isbeinginserted) |
					case when max(isbeingupdated) > 0 then 2 else 0 end |
					case when max(isbeingdeleted) > 0 then 4 else 0 end  
				 from UserRoleMember WHERE NodeId = @NodeId

		if( @UserRoleMemberFlag & 4 ) = 4
			DELETE FROM UserRoleMember WHERE NodeId = @NodeId and IsBeingDeleted = 1

		if ( @EmptyTables & 128 ) != 128
		begin
			select @UserRoleNodeFlag =  max(isbeinginserted) |
						case when max(isbeingupdated) > 0 then 2 else 0 end |
						case when max(isbeingdeleted) > 0 then 4 else 0 end  
					 from UserRoleNode WHERE NodeId = @NodeId

			if( @UserRoleNodeFlag & 4 ) = 4
				DELETE FROM UserRoleNode WHERE NodeId = @NodeId and IsBeingDeleted = 1
		end
	end
	
	/*if @nodeType = 0x00000400 or @nodeType = 0x80000800 or @nodeType = 0x00001000
		DELETE FROM GalleryControl WHERE NodeId = @NodeId and IsBeingDeleted = 1*/
	
	/* If we are not a shortcut... */
	if @nodetype <> 0x80000020
	begin
		if ( @EmptyTables & 16 ) = 0
		begin
			select @NodeResourceFlag =  max(isbeinginserted) |
						case when max(isbeingupdated) > 0 then 2 else 0 end |
						case when max(isbeingdeleted) > 0 then 4 else 0 end  
					 from NodeResource WHERE NodeId = @NodeId
			
			if( @NodeResourceFlag & 4 ) = 4
				DELETE FROM NodeResource WHERE NodeId = @NodeId and IsBeingDeleted = 1
		end

		if ( @EmptyTables & 4 ) = 0
		begin
			select @ControlPropertyFlag =  max(isbeinginserted) |
						case when max(isbeingupdated) > 0 then 2 else 0 end |
						case when max(isbeingdeleted) > 0 then 4 else 0 end  
					 from ControlProperty WHERE NodeId = @NodeId
			
			if( @ControlPropertyFlag & 4 ) = 4
				DELETE FROM ControlProperty WHERE NodeId = @NodeId and IsBeingDeleted = 1
		end

		if ( @EmptyTables & 32 ) = 0
		begin
			select @NodeControlFlag =  max(isbeinginserted) |
						case when max(isbeingupdated) > 0 then 2 else 0 end |
						case when max(isbeingdeleted) > 0 then 4 else 0 end  
					 from NodeControl WHERE NodeId = @NodeId
			
			if( @NodeControlFlag & 4 ) = 4
				DELETE FROM NodeControl WHERE NodeId = @NodeId and IsBeingDeleted = 1
		end

		if ( @EmptyTables & 64 ) = 0
		begin
			select @LayoutPropertyFlag =  max(isbeinginserted) |
						case when max(isbeingupdated) > 0 then 2 else 0 end |
						case when max(isbeingdeleted) > 0 then 4 else 0 end  
					 from LayoutProperty WHERE NodeId = @NodeId
			
			if( @LayoutPropertyFlag & 4 ) = 4
				DELETE FROM LayoutProperty WHERE NodeId = @NodeId and IsBeingDeleted = 1
		end

		if ( @EmptyTables & 8 ) = 0
		begin
			select @NodeLayoutFlag =  max(isbeinginserted) |
						case when max(isbeingupdated) > 0 then 2 else 0 end |
						case when max(isbeingdeleted) > 0 then 4 else 0 end  
					 from NodeLayout WHERE NodeId = @NodeId
			
			if( @NodeLayoutFlag & 4 ) = 4
				DELETE FROM NodeLayout WHERE NodeId = @NodeId and IsBeingDeleted = 1
		end
	end
	
	/* Every type of node is concerned with these settings: */
	if ( @EmptyTables & 1 ) = 0
	begin
		select @NodeRoleFlag =  max(isbeinginserted) |
					case when max(isbeingupdated) > 0 then 2 else 0 end |
					case when max(isbeingdeleted) > 0 then 4 else 0 end  
				 from NodeRole WHERE NodeId = @NodeId
		
		if( @NodeRoleFlag & 4 ) = 4
			DELETE FROM NodeRole WHERE NodeId = @NodeId and IsBeingDeleted = 1
	end

	if ( @EmptyTables & 2 ) = 0
	begin
		select @NodePropertyFlag =  max(isbeinginserted) |
					case when max(isbeingupdated) > 0 then 2 else 0 end |
					case when max(isbeingdeleted) > 0 then 4 else 0 end  
				 from NodeProperty WHERE NodeId = @NodeId
		
		if( @NodePropertyFlag & 4 ) = 4
			DELETE FROM NodeProperty WHERE NodeId = @NodeId and IsBeingDeleted = 1
	end


	/*
	 * update the data being updated.
	 */
	
	/* Layout Templates */
	if @nodeType = 0x10000 and ( @LayoutTemplatePropertyFlag & 2 ) = 2
	begin
		update LayoutTemplateProperty set  isbeingupdated = 0, PropName = NewPropName, PropValue = NewPropValue, IsControl = NewIsControl, ControlID = NewControlID  WHERE NodeId = @NodeId and isbeingupdated = 1
	end

	/* User role */
	if @nodeType = 0x100000
	begin
		if ( @EmptyTables & 128 ) = 0 and ( @UserRoleNodeFlag & 2 ) = 2
			update UserRoleNode set  isbeingupdated = 0, Rights = NewRights WHERE NodeId = @NodeId and isbeingupdated = 1
		
		if(  @UserRoleMemberFlag & 2 ) = 2
			update UserRoleMember set  isbeingupdated = 0 WHERE NodeId = @NodeId and isbeingupdated = 1
	end

	/*if @nodeType = 0x00000400 or @nodeType = 0x80000800 or @nodeType = 0x00001000
		update GalleryControl set  isbeingupdated = 0, ActiveXGUID = NewActiveXGUID, Codebase = NewCodebase, Name = NewName, Platform = newPlatform, Version = NewVersion  WHERE NodeId = @NodeId and  isbeingupdated = 1
	*/

	/* If we are not a shortcut... */
	if @nodetype <> 0x80000020
	begin
		if ( @EmptyTables & 8 ) = 0 and ( @NodeLayoutFlag & 2 ) = 2
			update NodeLayout set  isbeingupdated = 0, Name = NewName, LayoutTemplateGUID = NewLayoutTemplateGUID  WHERE NodeId = @NodeId and  isbeingupdated = 1
		if ( @EmptyTables & 64 ) = 0 and ( @LayoutPropertyFlag & 2 ) = 2
			update LayoutProperty set  isbeingupdated = 0,PropName = NewPropName, IsControl = NewIsControl, PropValue = NewPropValue, ControlID = NewControlID  WHERE NodeId = @NodeId and isbeingupdated = 1
		if ( @EmptyTables & 32 ) = 0 and ( @NodeControlFlag & 2 ) = 2
			update NodeControl set  isbeingupdated = 0, Name = NewName  WHERE NodeId = @NodeId and  isbeingupdated = 1
		if ( @EmptyTables & 4 ) = 0 and ( @ControlPropertyFlag & 2 ) = 2
			update ControlProperty set  isbeingupdated = 0, PropName = NewPropName, IsResource = NewIsResource, PropValue = NewPropValue, ResourceID = NewResourceID  WHERE NodeId = @NodeId and isbeingupdated = 1
		
		if ( @EmptyTables & 16 ) = 0 and ( @NodeResourceFlag & 2 ) = 2
			update NodeResource set isbeingupdated = 0,Name = NewName, IsLink = NewIsLink, URL = NewURL, ResourceBlobID = NewResourceBlobID, NodeGUID = NewNodeGUID, LinkAnchor = NewLinkAnchor WHERE NodeId = @NodeId and isbeingupdated = 1
	end

	/* Every type of node is concerned with the following: */
	if ( @EmptyTables & 2 ) = 0 and ( @NodePropertyFlag & 2 ) = 2
		update NodeProperty set isbeingupdated = 0,  propvalue = newpropvalue,
		propvaluelong = newpropvaluelong
		WHERE NodeId = @NodeId and isbeingupdated = 1
	if ( @EmptyTables & 1 ) = 0 and ( @NodeRoleFlag & 2 ) = 2
		update NodeRole set isbeingupdated = 0 WHERE NodeId = @NodeId and isbeingupdated = 1



	/*
	 * update the data being inserted. 
   	 */

	/* Layout Templates */
	if @nodeType = 0x10000 and ( @LayoutTemplatePropertyFlag & 1 ) = 1
	begin
		update LayoutTemplateProperty  set isbeinginserted = 0  WHERE NodeId = @NodeId and isbeinginserted = 1
	end

	/* User role */
	if @nodeType = 0x100000
	begin
		if ( @EmptyTables & 128 ) = 0 and ( @UserRoleNodeFlag & 1 ) = 1
			update UserRoleNode set isbeinginserted = 0  WHERE NodeId = @NodeId and isbeinginserted = 1

		if ( @UserRoleMemberFlag & 1 ) = 1 
			update UserRoleMember set isbeinginserted = 0  WHERE NodeId = @NodeId and isbeinginserted = 1
	end

	/*if @nodeType = 0x00000400 or @nodeType = 0x80000800 or @nodeType = 0x00001000	
		update GalleryControl  set isbeinginserted = 0  WHERE NodeId = @NodeId and isbeinginserted = 1
	*/

	/* If we are not a shortcut... */
	if @nodetype <> 0x80000020
	begin
		if ( @EmptyTables & 8 ) = 0 and ( @NodeLayoutFlag & 1 ) = 1
			update NodeLayout  set isbeinginserted = 0  WHERE NodeId = @NodeId and isbeinginserted = 1
		if ( @EmptyTables & 64 ) = 0 and ( @LayoutPropertyFlag & 1 ) = 1
			update LayoutProperty  set isbeinginserted = 0  WHERE NodeId = @NodeId and isbeinginserted = 1
		if ( @EmptyTables & 32 ) = 0 and ( @NodeControlFlag & 1 ) = 1
			update NodeControl  set isbeinginserted = 0  WHERE NodeId = @NodeId and isbeinginserted = 1
		if ( @EmptyTables & 4 ) = 0 and ( @ControlPropertyFlag & 1 ) = 1
			update ControlProperty  set isbeinginserted = 0  WHERE NodeId = @NodeId and isbeinginserted = 1
		if ( @EmptyTables & 16 ) = 0 and ( @NodeResourceFlag & 1 ) = 1
			update NodeResource  set isbeinginserted = 0  WHERE NodeId = @NodeId and isbeinginserted = 1
	end

	/* All nodes are concerned with the following: */
	if ( @EmptyTables & 1 ) = 0 and ( @NodeRoleFlag & 1 ) = 1
		update NodeRole  set isbeinginserted = 0  WHERE NodeId = @NodeId and isbeinginserted = 1
	if ( @EmptyTables & 2 ) = 0 and ( @NodePropertyFlag & 1 ) = 1
		update NodeProperty set isbeinginserted = 0  WHERE NodeId = @NodeId and isbeinginserted = 1



	/* 
	 * Update the node table itself 
	 */
	DECLARE @dateGMT datetime, @dateCurrent datetime
	select @dateCurrent = GETDATE()
	
	exec GetGMTTime @dateCurrent, @dateGMT out

	if @KeepLocked = 1
	begin
		UPDATE Node SET ModifiedByUserId =@UserID, IsBeingInserted = 0, 
		 	IsHighPriority = NewIsHighPriority, SameRightsAsParent = newSameRightsAsParent, ReadyForApproval = NewReadyForApproval, SortOrdinal = NewSortOrdinal, ApprovalStatus = NewApprovalStatus, ApprovedByUserID  = NewApprovedByUserID, OwnerID = NewOwnerID, EffectiveDate = NewEffectiveDate, ExpiryDate = NewExpiryDate, TemplateGUID = NewTemplateGUID, ControlType = NewControlType, EmitterGUID = NewEmitterGUID, RuntimeControlGUID = NewRuntimeControlGUID, DesigntimeControlGUID = NewDesigntimeControlGUID, Name = newName, TemplateType = NewTemplateType, 
			DirectoryService = NewDirectoryService, DirectoryGUID = NewDirectoryGUID,
			IsHiddenModePublished = newIsHiddenModePublished, IsRobotFollowable = newIsRobotFollowable,
			IsRobotIndexable = newIsRobotIndexable, DisplayName = newDisplayName,
			ModifiedWhen = @dateGMT,
			ApprovedWhen = CASE IsCheckedOutVersion WHEN 0 THEN @dateGMT ELSE NULL END
			FROM NodeLock WHERE Node.Id = @NodeID AND Node.Id = NodeLock.NodeId
	end

	if @KeepLocked = 0
	begin
		UPDATE Node SET ModifiedByUserId =@UserID, IsLocked = 0, IsBeingInserted = 0,LockMachinename = NULL, LockUserId = NULL,
			IsHighPriority = NewIsHighPriority, SameRightsAsParent = newSameRightsAsParent, 
			ReadyForApproval = NewReadyForApproval, SortOrdinal = NewSortOrdinal, 
			ApprovalStatus = NewApprovalStatus, ApprovedByUserID  = NewApprovedByUserID, 
			OwnerID = NewOwnerID, EffectiveDate = NewEffectiveDate, ExpiryDate = NewExpiryDate, 
			TemplateGUID = NewTemplateGUID, ControlType = NewControlType, EmitterGUID = NewEmitterGUID, 
			RuntimeControlGUID = NewRuntimeControlGUID, DesigntimeControlGUID = NewDesigntimeControlGUID,
			Name = newName, TemplateType = NewTemplateType, DirectoryService = NewDirectoryService, DirectoryGUID = NewDirectoryGUID,
			IsHiddenModePublished = newIsHiddenModePublished, IsRobotFollowable = newIsRobotFollowable,
			IsRobotIndexable = newIsRobotIndexable, DisplayName = newDisplayName,
			ModifiedWhen = @dateGMT, 
			ApprovedWhen = CASE IsCheckedOutVersion WHEN 0 THEN @dateGMT ELSE NULL END
			 FROM NodeLock WHERE Id = @NodeID AND NodeLock.NodeId = Node.Id	
		DELETE FROM NodeLock WHERE NodeId = @NodeID
	end

GO


if substring(@@VERSION, PATINDEX('%_.__.___%', @@VERSION), 1) = '8'
	exec sp_executesql N'CREATE PROC RetrieveGMTTime with encryption as set nocount on select getutcdate() as GMTTime'
else
	exec sp_executesql N'
	CREATE PROC RetrieveGMTTime
	AS
	set nocount on
	Declare @curdate datetime,
		@newdate datetime,
		@offset int
	select @curdate = GetDate()
	Select @offset = DeltaGMT from utility
	select @newdate = DateAdd(minute,@offset,@curdate)
	select @newdate as GMTTime'
go


Create Procedure CreateArchive
	(
		@NewGUID char(38),
		@SourceGUID char(38),
		@SourceNodeId int,
		@ArchiveParentGUID char(38)
	)
	with encryption

As
	set nocount on

	DECLARE @dateGMT datetime, @dateCurrent datetime
	
	select @dateCurrent = GETDATE()
	
	exec GetGMTTime @dateCurrent, @dateGMT out
	
		INSERT INTO Node (IsLocked, IsCheckedOut, IsCheckedOutVersion, HasCheckedInVersion, 
			CheckedInVersionHasExisted, IsShortcut, IsBeingInserted,IsHighPriority,SameRightsAsParent,
			ReadyForApproval,ArchiveState,ApprovalStatus,Type,SortOrdinal,NodeGUID,	  ParentGUID, 
			FollowGUID,  DeletedWhen, DeletedByUserId, CreatedByUserId, CreatedWhen, ModifiedByUserId,
			ModifiedWhen,	OwnerId, ApprovedByUserId, EffectiveDate, ExpiryDate, TemplateGUID, 
			DerivedFromGUID, ControlType, EmitterGUID, RuntimeControlGUID,	DesigntimeControlGUID, 
			ParserGUID, IsBaseTemplate, ArchiveSourceGUID, ArchivedParentGUID, ApprovedWhen, ArchivedWhen, 
			LockUserId, LastChangeTime,      LockMachinename, Name, TemplateType, Mimetype, 
			DirectoryService, DirectoryGUID, IsHiddenModePublished, IsRobotFollowable, IsRobotIndexable, DisplayName) 
		SELECT  0, 0, 0, 1, 
			1, IsShortcut, 0, IsHighPriority,SameRightsAsParent,
			ReadyForApproval,ArchiveState,ApprovalStatus,Type,SortOrdinal,@NewGUID, @ArchiveParentGUID,
			FollowGUID, NULL, NULL, CreatedByUserId, @dateGMT, ModifiedByUserId, 
			ModifiedWhen,	OwnerId, ApprovedByUserId, EffectiveDate, ExpiryDate, TemplateGUID, 
			DerivedFromGUID, ControlType, EmitterGUID, RuntimeControlGUID,	DesigntimeControlGUID, 
			ParserGUID, IsBaseTemplate, @SourceGUID, ArchivedParentGUID, ApprovedWhen, @dateGMT, 
			NULL,           LastChangeTime, NULL,            Name, TemplateType, Mimetype, DirectoryService, DirectoryGUID,
			IsHiddenModePublished, IsRobotFollowable, IsRobotIndexable, DisplayName 
			FROM Node WHERE Id=@SourceNodeId
	
	Declare @NewNodeID int
	Declare @NodeType int
	
	select @NewNodeID = id, @NodeType = type  from node where nodeguid = @NewGUID
	
	Exec CopyAssociatedData @SourceNodeId, @NewNodeID, 0, @NodeType, 0
GO

CREATE Procedure CreateArchiveFromCheckedIn
(
	@NewGUID char(38),
	@NodeGUID char(38),
	@SourceNodeId int,
	@ArchiveParentGUID char(38)
)
with encryption

As
	set nocount on

	DECLARE @dateGMT datetime, @dateCurrent datetime
	
	select @dateCurrent = GETDATE()
	
	exec GetGMTTime @dateCurrent, @dateGMT out
	
	update node set NodeGUID = @NewGUID, 
			 ParentGUID	= @ArchiveParentGUID,
       		 IsCheckedOut = 0,
			 CreatedWhen = @dateGMT, 
			 ArchivedWhen = @dateGMT,
			 ArchiveSourceGUID = @NodeGUID
	where Id = @SourceNodeId
GO

create procedure GetRevisionCause
		(
		@PostingGUID char(38),
		@RevisionDate datetime
		)
	with encryption
as
/**
	@exception - Couldn't find the requested posting in the database
	@exception - RevisionDate is before the date when the posting was created
	@exception - Posting has never been published, so no revisions can exist
	@exception - RevisionDate is older than the last revision purge
 */
	declare @HasCheckedInVersion tinyint
	declare @CreatedDate datetime, @LastPurgeDate datetime
	declare @FollowGUID char(38), @TemplateGUID char(38)
	declare @PageFirstApproveDate datetime
	declare @SQL varchar(255)

	set nocount on
	
	set @RevisionDate = dateadd(ss, 1, @RevisionDate)

	/*check if RevisionDate is older than the last revision purge*/
	select @LastPurgeDate = LastRevisionPurge from utility

	if @LastPurgeDate is not null and @LastPurgeDate >= @RevisionDate 
		return 1	/*The specified date is older than the last revision purge date*/

	/*do some checking on the posting*/
	select @HasCheckedInversion = HasCheckedInVersion, @CreatedDate = CreatedWhen, 
	@FollowGUID = FollowGUID, @TemplateGUID = TemplateGUID
	from node where nodeguid = @PostingGUID
	
	if @@rowcount = 0
		return 2 	/*this posting doesn't exist in our database */

	if @HasCheckedInversion = 0 
		return 3	/*Posting has never been published */

	select @PageFirstApproveDate = min(ApprovedWhen) from node
	where (nodeguid = @FollowGUID and IsCheckedOutVersion = 0 and ArchiveSourceGUID is null)	--cuurent version 
	 or (ArchiveSourceGUID = @FollowGUID )

	if @CreatedDate > @RevisionDate or @PageFirstApproveDate > @RevisionDate
		return 4	/*RevisionDate is before the date when the posting was created */
	

	select top 1 rev.NodeGUID, rev.ApprovedWhen as RevisionTime
	from (
		/*get the posting itself in case if nothing that it depends on has been archived yet*/
		select NodeGUID, CreatedWhen as ApprovedWhen, 10 as RevisionSortOrder from node where nodeguid = @PostingGUID	
		UNION ALL

		/*get revisions of the posting's page*/
		select @PostingGUID, PageRev.ApprovedWhen, 1 as RevisionSortOrder 
		from node PageRev 
		where ( (PageRev.NodeGUID = @FollowGUID and PageRev.ArchiveSourceGUID is null )
			or (PageRev.ArchiveSourceGUID is not null and PageRev.ArchiveSourceGUID = @FollowGUID)) and 
		PageRev.ApprovedWhen <= @RevisionDate
		and PageRev.ApprovedWhen > @LastPurgeDate

		UNION ALL
		/*get revisions of the template*/
		select case TemplateRev.ArchiveSourceGUID when null then TemplateRev.NodeGUID
					else TemplateRev.ArchiveSourceGUID end as NodeGUID, 
					TemplateRev.ApprovedWhen, 2 as RevisionSortOrder 
		from node TemplateRev
		where ( (TemplateRev.NodeGUID = @TemplateGUID and TemplateRev.ArchiveSourceGUID is null )
			or (TemplateRev.ArchiveSourceGUID is not null and TemplateRev.ArchiveSourceGUID = @TemplateGUID)) and 
		TemplateRev.ApprovedWhen <= @RevisionDate		
		and TemplateRev.ApprovedWhen > @LastPurgeDate

		/*get revisions of the page resources*/
		UNION ALL
		select case ResourceRev.ArchiveSourceGUID when null then ResourceRev.NodeGUID
				else ResourceRev.ArchiveSourceGUID end as NodeGUID,
				ResourceRev.ApprovedWhen, 3 as RevisionSortOrder 
		from Node Page, NodeResource, Node ResourceRev
		where (Page.NodeGUID = @FollowGUID or Page.ArchiveSourceGUID = @FollowGUID) 
		and Page.Id = NodeResource.NodeID and 
		NodeResource.NodeGUID = ResourceRev.ArchiveSourceGUID 
			and (ResourceRev.ApprovedWhen < Page.ArchivedWhen or Page.ArchivedWhen is null)
			and ResourceRev.ApprovedWhen >= Page.ApprovedWhen
		and ResourceRev.ApprovedWhen <= @RevisionDate
		and ResourceRev.ApprovedWhen > @LastPurgeDate

		UNION ALL
		select case ResourceRev.ArchiveSourceGUID when null then ResourceRev.NodeGUID
				else ResourceRev.ArchiveSourceGUID end as NodeGUID,
				ResourceRev.ApprovedWhen, 3 as RevisionSortOrder 
		from Node Page, NodeResource, Node ResourceRev
		where (Page.NodeGUID = @FollowGUID or Page.ArchiveSourceGUID = @FollowGUID) 
		and Page.Id = NodeResource.NodeID and 
		NodeResource.NodeGUID = ResourceRev.NodeGUID 
			and (ResourceRev.ApprovedWhen < Page.ArchivedWhen or Page.ArchivedWhen is null)
			and ResourceRev.ApprovedWhen >= Page.ApprovedWhen
		and ResourceRev.ApprovedWhen <= @RevisionDate
		and ResourceRev.ApprovedWhen > @LastPurgeDate

		/*get revisions of the template resources*/
		UNION ALL
		select case ResourceRev.ArchiveSourceGUID when null then ResourceRev.NodeGUID
				else ResourceRev.ArchiveSourceGUID end as NodeGUID,
				ResourceRev.ApprovedWhen, 4 as RevisionSortOrder 
		from Node Template, NodeResource, Node ResourceRev
		where (Template.NodeGUID = @TemplateGUID or Template.ArchiveSourceGUID = @TemplateGUID)
		and Template.Id = NodeResource.NodeID and 
		NodeResource.NodeGUID = ResourceRev.ArchiveSourceGUID 
			and (ResourceRev.ApprovedWhen < Template.ArchivedWhen or Template.ArchivedWhen is null)
			and ResourceRev.ApprovedWhen >= Template.ApprovedWhen
		and ResourceRev.ApprovedWhen <= @RevisionDate
		and ResourceRev.ApprovedWhen > @LastPurgeDate

		UNION ALL
		select case ResourceRev.ArchiveSourceGUID when null then ResourceRev.NodeGUID
				else ResourceRev.ArchiveSourceGUID end as NodeGUID,
				ResourceRev.ApprovedWhen, 4 as RevisionSortOrder 
		from Node Template, NodeResource, Node ResourceRev
		where (Template.NodeGUID = @TemplateGUID or Template.ArchiveSourceGUID = @TemplateGUID)
		and Template.Id = NodeResource.NodeID and 
		NodeResource.NodeGUID = ResourceRev.NodeGUID 
			and (ResourceRev.ApprovedWhen < Template.ArchivedWhen or Template.ArchivedWhen is null)
			and ResourceRev.ApprovedWhen >= Template.ApprovedWhen
		and ResourceRev.ApprovedWhen <= @RevisionDate
		and ResourceRev.ApprovedWhen > @LastPurgeDate

		) rev
	order by rev.ApprovedWhen desc, rev.RevisionSortOrder ASC
	return 0
		
go


create procedure PurgeRevisionsByDate
		(
		@RevisionDate datetime
		)
	with encryption
as
/**
	@exception - @RevisionDate is older than the most resent Revision Purge
 */
	declare @LastPurgeDate datetime
	declare @SQL varchar(255)

	set nocount on

	/*check if RevisionDate is older than the last revision purge*/
	select @LastPurgeDate = LastRevisionPurge from utility

	if @LastPurgeDate is not null and @LastPurgeDate >= @RevisionDate 
		return 1	/*RevisionDate is older than the last revision purge*/


	DECLARE @CurrentId as int
	
	DECLARE DeletedItems CURSOR LOCAL READ_ONLY 
	    FOR SELECT Id FROM Node WHERE ArchivedWhen <= @RevisionDate 
		and (type <> 256 or (type = 256 and nodeguid not in (select nodeguid from noderesource where nodeguid is not null) )) 
	
	OPEN DeletedItems
	FETCH NEXT FROM DeletedItems INTO @CurrentId
	
	WHILE @@FETCH_STATUS = 0
	  BEGIN
			DELETE FROM NodeProperty WHERE NodeId = @CurrentId
			DELETE FROM NodeRole WHERE NodeId = @CurrentId
			DELETE FROM NodeResource WHERE NodeId = @CurrentId
			DELETE FROM ControlProperty WHERE NodeId = @CurrentId
			DELETE FROM NodeControl WHERE NodeId = @CurrentId
			DELETE FROM LayoutProperty WHERE NodeId = @CurrentId
			DELETE FROM NodeLayout WHERE NodeId = @CurrentId
			DELETE FROM GalleryControl WHERE NodeId = @CurrentId
			DELETE FROM LayoutTemplateProperty WHERE NodeId = @CurrentId
			DELETE FROM NodeRole WHERE NodeId = @CurrentId
			DELETE FROM UserRoleMember WHERE NodeId = @CurrentId
			DELETE FROM UserRoleNode WHERE NodeId = @CurrentId
			DELETE FROM NodeLock WHERE NodeId = @CurrentId
			DELETE FROM Node WHERE Id = @CurrentId
			
			FETCH NEXT FROM DeletedItems INTO @CurrentId
	  END  

	DECLARE @dateGMT datetime, @dateCurrent datetime
	select @dateCurrent = GETDATE()
	exec GetGMTTime @dateCurrent, @dateGMT out
	
	if @RevisionDate > @dateGMT
		set @RevisionDate = @dateGMT

	if exists (select * from utility )
		update utility set LastRevisionPurge = @RevisionDate
	else
		insert into utility (LastRevisionPurge) values(@RevisionDate)

	close DeletedItems
	deallocate DeletedItems

	return 0			
go


create  procedure GetRevisionHistory
		(
		@PostingGUID char(38),
		@IncludeDependency int
		)
with encryption
as
	/* These are the values for @IncludeDependency:
	PAGE			= 1,
	PAGE_RESOURCES	= 2,
	TEMPLATE		= 4,
	TEMPLATE_RESOURCES = 8
	*/

/**
	@exception - Posting doesn't exist in the database
 */
	set nocount on
	declare @LastPurgeDate datetime

	if not exists(select id from node where nodeguid = @PostingGUID)
		return 2	/*this posting doesn't exist in our database */


	create table #Revisions
		(
		NodeGUID char(38) not null,	
		ArchiveSourceGUID char(38) null,	
		CreatedWhen datetime null,
		ArchivedWhen datetime null,
		ApprovedWhen datetime null,		
		Type tinyint not null
		)

	create index Revisions_NodeGUID on #Revisions
		(
		NodeGUID
		)

	declare @PostingCreateDate datetime
	declare @PostingFollowGUID char(38)
	declare @PostingTemplateGUID char(38)
	declare @PageFirstApproveDate datetime

	select @LastPurgeDate = LastRevisionPurge from utility

	select @PostingFollowGUID = FollowGUID, @PostingTemplateGUID = TemplateGUID,
	@PostingCreateDate = CreatedWhen from Node where NodeGUID = @PostingGUID

	select @PageFirstApproveDate = min(ApprovedWhen) from node
	where (nodeguid = @PostingFollowGUID and IsCheckedOutVersion = 0 and ArchiveSourceGUID is null)	--cuurent version 
	 or (ArchiveSourceGUID = @PostingFollowGUID )

	/*first get all revisions of the posting's page*/
	if (@IncludeDependency & 1 ) = 1 or (@IncludeDependency & 2 ) = 2
	begin
		/*get the checked in version of the page */
		insert into #Revisions (NodeGUID, ArchiveSourceGUID, CreatedWhen, ApprovedWhen, Type)
		select Pages.NodeGUID, @PostingGUID, Pages.CreatedWhen, Pages.ApprovedWhen, 1 
		from Node Pages 
		where Pages.NodeGUID = @PostingFollowGUID and
		Pages.IsCheckedOutVersion = 0  
		
		/*get page revisions*/
		insert into #Revisions (NodeGUID, ArchiveSourceGUID, ArchivedWhen, ApprovedWhen, Type)
		select Pages.NodeGUID, @PostingGUID, Pages.ArchivedWhen, Pages.ApprovedWhen, 1 
		from Node Pages 
		where Pages.ArchiveSourceGUID = @PostingFollowGUID 
	end

	/*now let's find resource revisions of the page*/
	if (@IncludeDependency & 2 ) = 2
	begin
		/*get current version of the resource*/
		insert into #Revisions (NodeGUID, ArchiveSourceGUID, CreatedWhen, ApprovedWhen, Type)
		select distinct Resources.NodeGUID, Resources.NodeGUID, Resources.CreatedWhen, Resources.ApprovedWhen, 2
		from Node Resources, Node Pages, NodeResource, #Revisions
		where #Revisions.NodeGUID = Pages.NodeGUID and #Revisions.Type = 1 and 
		Pages.IsCheckedOutVersion = 0 and Pages.ID = NodeResource.NodeId and 
		NodeResource.NodeGUID = Resources.NodeGUID
		and (Resources.ApprovedWhen < #Revisions.ArchivedWhen or #Revisions.ArchivedWhen is null)
		and Resources.ApprovedWhen >= #Revisions.ApprovedWhen

		/*get resource revisions*/
		insert into #Revisions (NodeGUID, ArchiveSourceGUID, ArchivedWhen, ApprovedWhen, Type)
		select distinct Resources.NodeGUID, Resources.ArchiveSourceGUID, Resources.ArchivedWhen, Resources.ApprovedWhen, 2
		from Node Resources, Node Pages, NodeResource, #Revisions
		where #Revisions.NodeGUID = Pages.NodeGUID and #Revisions.Type = 1 and 
		Pages.ID = NodeResource.NodeId and 
		NodeResource.NodeGUID = Resources.ArchiveSourceGUID
		and (Resources.ApprovedWhen < #Revisions.ArchivedWhen or #Revisions.ArchivedWhen is null)
		and Resources.ApprovedWhen >= #Revisions.ApprovedWhen

	end

	/*get revisions of the posting's template*/
	if (@IncludeDependency & 4 ) = 4 or (@IncludeDependency & 8 ) = 8
	begin
		/*get current revision of the template*/
		insert into #Revisions (NodeGUID, ArchiveSourceGUID, CreatedWhen, ApprovedWhen, Type)
		select Templates.NodeGUID, Templates.NodeGUID, Templates.CreatedWhen, Templates.ApprovedWhen, 4 
		from Node Templates 
		where Templates.NodeGUID = @PostingTemplateGUID
		and Templates.IsCheckedOutVersion = 0
		
		/*get template revisions*/
		insert into #Revisions (NodeGUID, ArchiveSourceGUID, ArchivedWhen, ApprovedWhen, Type)
		select Templates.NodeGUID, Templates.ArchiveSourceGUID, Templates.ArchivedWhen, Templates.ApprovedWhen, 4 
		from Node Templates 
		where Templates.ArchiveSourceGUID = @PostingTemplateGUID 

	end

	if (@IncludeDependency & 8 ) = 8
	begin
		/*get current verion of template resources*/
		insert into #Revisions (NodeGUID, ArchiveSourceGUID, CreatedWhen, ApprovedWhen, Type)
		select distinct Resources.NodeGUID, Resources.NodeGUID, Resources.CreatedWhen, Resources.ApprovedWhen, 8
		from Node Resources, Node Templates, NodeResource, #Revisions
		where #Revisions.NodeGUID = Templates.NodeGUID and #Revisions.Type = 4 and 
		Templates.IsCheckedOutversion = 0 and Templates.ID = NodeResource.NodeId and 
		NodeResource.NodeGUID = Resources.NodeGUID
		and (Resources.ApprovedWhen < #Revisions.ArchivedWhen or #Revisions.ArchivedWhen is null)
		and Resources.ApprovedWhen >= #Revisions.ApprovedWhen

		/*get resource revisions*/
		insert into #Revisions (NodeGUID, ArchiveSourceGUID, ArchivedWhen, ApprovedWhen, Type)
		select distinct Resources.NodeGUID, Resources.ArchiveSourceGUID, Resources.ArchivedWhen, Resources.ApprovedWhen, 8
		from Node Resources, Node Templates, NodeResource, #Revisions
		where #Revisions.NodeGUID = Templates.NodeGUID and #Revisions.Type = 4 and 
		Templates.ID = NodeResource.NodeId and 
		NodeResource.NodeGUID = Resources.ArchiveSourceGUID
		and (Resources.ApprovedWhen < #Revisions.ArchivedWhen or #Revisions.ArchivedWhen is null)
		and Resources.ApprovedWhen >= #Revisions.ApprovedWhen

	end

	select distinct ArchiveSourceGUID as NodeGUID, ApprovedWhen as RevisionTime
	from #Revisions
	where (@IncludeDependency & Type) = Type 
	and ApprovedWhen >= @PostingCreateDate and ApprovedWhen >= @PageFirstApproveDate
	and ApprovedWhen > @LastPurgeDate
	order by RevisionTime Asc

	return 0
			

GO

create procedure LockForRead 
with encryption
as
set nocount on
SET LOCK_TIMEOUT 0 
DECLARE @IsLocked tinyint
select @IsLocked=IsLocked from ServerLock with(HOLDLOCK) where islocked=0
SET LOCK_TIMEOUT -1
GO

create procedure LockForWrite 
with encryption
as
DECLARE @IsLocked tinyint
update ServerLock set IsLocked = isLocked where isLocked = 0
/*select @IsLocked=IsLocked from ServerLock with(XLOCK) where islocked=0*/
GO

create procedure SearchPostingCustomProperties
	(
	@SearchMode int,
	@PropName nvarchar(50),
	@PropValue nvarchar(300),
	@IsCheckedOutVersion tinyint
	)
	with encryption
as
	set nocount on
/*
enum values for @SearchMode
	0 - Search property name only
	1 - Search property name and value	
*/
		if @SearchMode = 0 	/* search property name only */
		begin
			select Postings.NodeGUID, Postings.ModifiedWhen, Postings.Name, Postings.OwnerID, Postings.ParentGUID, Postings.Type, 
					Postings.IsShortcut, Postings.SameRightsAsParent, Postings.IsCheckedOutVersion
			from Node Postings, NodeProperty, Node Templates
			WHERE Postings.TemplateGuid = Templates.NodeGuid
			AND Templates.IsCheckedOutVersion = 0
			AND Templates.Id = NodeProperty.NodeId
		   	AND Postings.IsBeingInserted = 0
	       	AND PropName = @PropName
		end

		else	/*search property name and value*/
		begin
			select Postings.NodeGUID, Postings.ModifiedWhen, Postings.Name, Postings.OwnerID, Postings.ParentGUID, Postings.Type, 
					Postings.IsShortcut, Postings.SameRightsAsParent, Postings.IsCheckedOutVersion
			from Node Postings, NodeProperty, Node Pages
			WHERE Postings.FollowGuid = Pages.NodeGuid 
			AND Pages.IsCheckedOutVersion = @IsCheckedOutVersion
			AND Pages.Id = NodeId 
			AND Postings.IsBeingInserted = 0 
			AND PropName = @PropName
		    AND ( ((@PropValue is null or len(@PropValue) = 0) and (PropValue = '' or PropValue Is NULL)) or
					(len(@PropValue) > 0 and PropValue like @PropValue) )
			AND Postings.NodeGUID in
			(
			select Postings.NodeGUID
			from Node Postings, NodeProperty, Node Templates
			WHERE Postings.TemplateGuid = Templates.NodeGuid 
			AND Templates.IsCheckedOutVersion = 0
			AND Templates.Id = NodeProperty.NodeId
			AND Postings.IsBeingInserted = 0 
			AND PropName = @PropName
			)
				
			UNION
			/*now get postings that do not have overrides on the page*/
			select Postings.NodeGUID, Postings.ModifiedWhen, Postings.Name, Postings.OwnerID, Postings.ParentGUID, Postings.Type, 
					Postings.IsShortcut, Postings.SameRightsAsParent, Postings.IsCheckedOutVersion
			from Node Postings, NodeProperty, Node Templates
			WHERE Postings.TemplateGuid = Templates.NodeGuid 
			AND Templates.IsCheckedOutVersion = 0
			AND Templates.Id = NodeProperty.NodeId
			AND Postings.IsBeingInserted = 0 
			AND PropName = @PropName
		    AND ( ((@PropValue is null or len(@PropValue) = 0) and (PropValue = '' or PropValue Is NULL)) or
					(len(@PropValue) > 0 and PropValue like @PropValue) )
			AND Postings.NodeGUID not in
			(select Postings.NodeGUID
				from Node Postings, NodeProperty, Node Pages
				WHERE Postings.FollowGuid = Pages.NodeGuid 
				AND Pages.IsCheckedOutVersion = @IsCheckedOutVersion
				AND Pages.Id = NodeId 
				AND Postings.IsBeingInserted = 0 
				AND PropName = @PropName
			)

	end
go


CREATE Procedure ArchiveDeletedResource
(
	@NodeGUID char(38),
	@ArchiveParentGUID char(38)
)
with encryption
As
	set nocount on

	declare @SourceNodeId int
	set @SourceNodeId = 0
	
	/*check if this guid exists in the database and it's a resource gallery item that's been deleted*/
	select @SourceNodeId = Id from node where NodeGUID = @NodeGUID and ParentGUID = @ArchiveParentGUID and Type = 256
	and ArchiveSourceGUID is null

	if( @SourceNodeId is not null and @SourceNodeId > 0 )
	begin
		declare @newGUID char(38)
		set @newGUID = '{' + convert(varchar(38), NEWID()) + '}'	

		exec CreateArchiveFromCheckedIn @newGUID, @NodeGUID, @SourceNodeId, @ArchiveParentGUID

	end
GO


create procedure GetNextPublishingChangeTime
	(
	@NextChangeTime datetime out
	)
as
	declare @NextEffectiveDate datetime, @NextExpiryDate datetime

	DECLARE @dateGMT datetime, @dateCurrent datetime
	select @dateCurrent = GETDATE()
	exec GetGMTTime @dateCurrent, @dateGMT out

	select @NextEffectiveDate = min(EffectiveDate)
	from node
	where EffectiveDate > @dateGMT and ApprovalStatus = 1

	select @NextExpiryDate = min(ExpiryDate)
	from node
	where ExpiryDate > @dateGMT and ApprovalStatus = 1

	if @NextEffectiveDate <= @NextExpiryDate
		set @NextChangeTime = @NextEffectiveDate
	else if @NextExpiryDate < @NextEffectiveDate 
		set @NextChangeTime = @NextExpiryDate
	else	--one of these values is null
		set @NextChangeTime = isnull(COALESCE(@NextEffectiveDate, @NextExpiryDate ), '3000-01-01 00:00:00.000')

go

GRANT EXECUTE ON LockForRead TO Public
GRANT EXECUTE ON LockForWrite TO Public

GRANT EXECUTE ON DeleteNodeByID TO Public
GRANT EXECUTE ON DeleteNodeByGUID TO Public
GRANT EXECUTE ON CopyAssocDataToOtherVersion To Public
GRANT EXECUTE ON CopyAssociatedData TO Public
GRANT EXECUTE ON GetUserId TO Public
GRANT EXECUTE ON GetDescendants TO Public
GRANT EXECUTE ON DeleteRecursive TO Public
GRANT EXECUTE ON CommitNode TO Public
GRANT EXECUTE ON CheckoutNode TO Public
GRANT EXECUTE ON LockNode TO Public
GRANT EXECUTE ON RollbackLock TO Public
GRANT EXECUTE ON GetTimeZoneSetting TO Public
GRANT EXECUTE ON UpdateChangeTable TO Public
GRANT EXECUTE ON RetrieveGMTTime to public
GRANT EXECUTE ON GetGMTTime to public

GRANT EXECUTE ON CreateArchive TO Public
GRANT EXECUTE ON CreateArchiveFromCheckedIn TO Public
GRANT EXECUTE ON GetRevisionCause to public
GRANT EXECUTE ON PurgeRevisionsByDate to public
GRANT EXECUTE ON GetRevisionHistory to public
GRANT EXECUTE ON SearchPostingCustomProperties to public
grant execute on ArchiveDeletedResource to public
GRANT EXECUTE ON GetNextPublishingChangeTime TO Public
GO

exec sp_createstats @fullscan = 'fullscan'
go


/*********************************** Triggers *************************************/
create Trigger ChangeTrigger_Insert
	on dbo.Node
	FOR INSERT
	as
	 SET NOCOUNT ON

	DECLARE @dateGMT datetime, @dateCurrent datetime
	select @dateCurrent = GETDATE()
	exec GetGMTTime @dateCurrent, @dateGMT out

	DECLARE @InsertedId int, @InsertedNodeGUID char(38), @InsertedParentGUID char(38)
	DECLARE InsertedItems CURSOR LOCAL READ_ONLY FOR SELECT Id, NodeGUID, ParentGUID FROM inserted 
	OPEN InsertedItems

	FETCH NEXT FROM InsertedItems INTO @InsertedId, @InsertedNodeGUID, @InsertedParentGUID
 	WHILE @@FETCH_STATUS = 0
	BEGIN

		UPDATE Node SET 
			CreatedWhen = CASE Node.CreatedWhen WHEN  '1900-01-01 00:00:00' THEN @dateGMT ELSE Node.CreatedWhen END,
			ModifiedWhen = CASE Node.ModifiedWhen WHEN  '1900-01-01 00:00:00' THEN @dateGMT ELSE Node.ModifiedWhen END,
			ApprovedWhen = CASE Node.ApprovedWhen WHEN '1900-01-01 00:00:00' THEN @dateGMT ELSE Node.ApprovedWhen END,
			LastChangeTime = @dateGMT 
	 	FROM Node WHERE Node.Id =@InsertedId 

		if (SELECT OBJECT_ID('tempdb..#Sandbox')) IS NOT NULL
		BEGIN
			exec sp_executesql N'if exists( select NodeGUID from #Sandbox where NodeGUID = @GUID)
			begin
				Update #Sandbox set ParentGUID = @ParentGUID,
					Reason = 0,
					LastChangeTime = @GMT
				where #Sandbox.nodeguid = @GUID
			end
			else
			begin
				INSERT INTO #Sandbox ( NodeGuid, ParentGuid, Reason, LastChangeTime) 
					VALUES (@GUID, @ParentGuid, 0, @GMT) 
			end', N'@GUID  char(38), @ParentGuid char(38), @GMT datetime', 
			@GUID = @InsertedNodeGUID, @ParentGUID = @InsertedParentGuid, @GMT = @dateGMT
		end
		else
		begin
			print 'Warning: Database is being updated manually. This is not recommended.'
			print 'Execute UpdateChangeTable stored procedure after you finish your changes.'
		end

		FETCH NEXT FROM InsertedItems INTO @InsertedId, @InsertedNodeGUID, @InsertedParentGUID
	END

	close InsertedItems
	deallocate InsertedItems


GO

CREATE Trigger ChangeTrigger_Update
	on dbo.Node
	FOR UPDATE
	as
	 SET NOCOUNT ON

	if( (SELECT trigger_nestlevel()) > 1 and
		(SELECT trigger_nestlevel( object_ID('NodeLockTrigger_Update'))) = 0   )
		return

	 /*update LastChangeTime field in Node table*/
	DECLARE @dateGMT datetime, @dateCurrent datetime
	select @dateCurrent = GETDATE()
	exec GetGMTTime @dateCurrent, @dateGMT out

	DECLARE @InsertedId int, @InsertedNodeGUID char(38), @InsertedParentGUID char(38)
	DECLARE InsertedItems CURSOR LOCAL READ_ONLY FOR SELECT Id, NodeGUID, ParentGUID FROM inserted 
	OPEN InsertedItems

	FETCH NEXT FROM InsertedItems INTO @InsertedId, @InsertedNodeGUID, @InsertedParentGUID
 	WHILE @@FETCH_STATUS = 0
	BEGIN

		UPDATE Node SET 
			CreatedWhen = CASE Node.CreatedWhen WHEN  '1900-01-01 00:00:00' THEN @dateGMT ELSE Node.CreatedWhen END,
			ModifiedWhen = CASE Node.ModifiedWhen WHEN  '1900-01-01 00:00:00' THEN @dateGMT ELSE Node.ModifiedWhen END,			
			ApprovedWhen = CASE Node.ApprovedWhen WHEN '1900-01-01 00:00:00' THEN @dateGMT ELSE Node.ApprovedWhen END,
			LastChangeTime = @dateGMT 
	 	FROM Node WHERE Node.Id =@InsertedId 

		if (SELECT OBJECT_ID('tempdb..#Sandbox')) IS NOT NULL
		BEGIN
			exec sp_executesql N'if exists( select NodeGUID from #Sandbox where NodeGUID = @GUID)
			begin
				Update #Sandbox set ParentGUID = @ParentGUID,
						Reason = 1,
						LastChangeTime = @GMT
				where #Sandbox.nodeguid = @GUID
			end
			else
			begin
				INSERT INTO #Sandbox ( NodeGuid, ParentGuid, Reason, LastChangeTime) 
					VALUES (@GUID, @ParentGuid, 1, @GMT) 
			end', N'@GUID  char(38), @ParentGuid char(38), @GMT datetime', 
			@GUID = @InsertedNodeGUID, @ParentGUID = @InsertedParentGuid, @GMT = @dateGMT
		end
		else
		begin
			print 'Warning: Database is being updated manually. This is not recommended.'
			print 'Execute UpdateChangeTable stored procedure after you finish your changes.'
		end

		FETCH NEXT FROM InsertedItems INTO @InsertedId, @InsertedNodeGUID, @InsertedParentGUID
	END
GO

create Trigger ChangeTrigger_Delete
on dbo.Node
FOR DELETE
as
 SET NOCOUNT ON
 
if (SELECT OBJECT_ID('tempdb..#Sandbox')) IS NOT NULL
BEGIN
	 /* update the corresponding entry into the ChangeTable*/	  
	DECLARE @dateGMT datetime, @dateCurrent datetime
	select @dateCurrent = GETDATE()
	
	exec GetGMTTime @dateCurrent, @dateGMT out
	
	DECLARE @DeletedNodeGUID char(38), @DeletedParentGUID char(38)
	DECLARE DeletedItems CURSOR LOCAL READ_ONLY FOR 
		select NodeGUID, ParentGUID from deleted dl where not exists (select id from node where nodeguid = dl.nodeguid) 
	OPEN DeletedItems

	FETCH NEXT FROM DeletedItems INTO @DeletedNodeGUID, @DeletedParentGUID
 	WHILE @@FETCH_STATUS = 0
	BEGIN
		exec sp_executesql N'if exists( select NodeGUID from #Sandbox where NodeGUID = @DeletedNodeGUID)
			begin
				Update #Sandbox set ParentGUID = @DeletedParentGUID,
				Reason = 2,
				LastChangeTime = @dateGMT
				where #Sandbox.nodeguid  = @DeletedNodeGUID
			end
			else
			begin
				INSERT INTO #Sandbox ( NodeGuid, ParentGuid, Reason, LastChangeTime) 
				VALUES (@DeletedNodeGUID, @DeletedParentGUID, 2, @dateGMT)
			end',
		N'@DeletedNodeGUID char(38), @DeletedParentGUID char(38), @dateGMT datetime',
		@DeletedNodeGUID, @DeletedParentGUID, @dateGMT

		FETCH NEXT FROM DeletedItems INTO @DeletedNodeGUID, @DeletedParentGUID
	END

	close DeletedItems
	deallocate DeletedItems

end
else
begin
	print 'Warning: Database is being updated manually. This is not recommended.'
	print 'Execute UpdateChangeTable stored procedure after you finish your changes.'
end
GO

CREATE Trigger NodeLockTrigger_Update
on dbo.NodeLock
FOR UPDATE
as
 SET NOCOUNT ON
 
/*Execute an update on Node table to trigger LastChangeTime update*/
 UPDATE Node SET IsLocked = IsLocked
 from inserted
 WHERE Node.Id = inserted.nodeId  

GO


create Trigger UpperUserName
on dbo.AEUser
FOR INSERT, UPDATE
as
 SET NOCOUNT ON

 UPDATE AEUser SET UserNameUpper = UPPER(RTRIM(AEUser.UserName))
 FROM AEUser, inserted
 WHERE AEUser.UserID = inserted.userid 

GO

exec GetTimeZoneSetting
go